sqlsql-server-2016sqldatetimesql-convert

Converting string to datetime works with select but nor update (T-SQL)


I have a table in my database that has has both the original user input date string, then tries to get the datetime from that by using CONVERT

CREATE TABLE #UserInput 
(
    actualDate DATETIME NULL,
    dateString VARCHAR(50) NULL
)

The statement

SELECT CONVERT(DATETIME, dateString) 
FROM #UserInput 

works fine, and correctly converts the strings to datetime.

However, when I try to set the actualDate column, using the statement

UPDATE X 
SET X.actualDate = CONVERT(DATETIME, X.dateString) 
FROM #UserInput X 

I get the error:

Conversion failed when converting date and/or time from character string.

Since I can run the select, I know all the dateStrings are formatted correctly and can be converted. So then why am I unable to do so with the update?

The specific format I've been testing with is mm/dd/yyyy hh:mm, but the solution would need to handle other formats as well.

I appreciate any help on this, thanks.


Solution

  • CREATE TABLE #UserInput (
    actualDate datetime NULL,
    dateString varchar(50) NULL)
    
    insert #UserInput(dateString) values('20181231 12:15')
    
    SELECT CONVERT(datetime, dateString) 
    FROM #UserInput 
    
    UPDATE X 
    SET X.actualDate = CONVERT(datetime, X.dateString) 
    FROM #UserInput X 
    
    select * from #UserInput
    

    I tested it for two format yyyymmdd hh:mm and mm/dd/yyyy hh:mm this code working correctly. I didn't get any error for update or select run.