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

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.