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

Question

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.

Have you tried calling CONVERT with the style argument to see if that makes any difference? — Joakim Danielson, Nov 30 '18 at 18:46

score 1 · Accepted Answer · answered Nov 30 '18 at 18:50

1

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.

answered Nov 30 '18 at 18:50

Caner Arslandoğmuş

36
4

Turns out it was working correctly the whole time, I was getting the same error elsewhere and I hadn't realized it. Sorry for starting this goose chase – M. McCrary Nov 30 '18 at 20:09

score 0 · Answer 2 · answered Nov 30 '18 at 19:22

0

The simply solution is try_convert():

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

I suspect the problem is that you are not seeing all the rows in X -- just a few that match. To test this, run:

select X.*
from #userinput X
where try_convert(datetime, x.dateString) is null;

answered Nov 30 '18 at 19:22

Gordon Linoff

1,242,037
58
646
786

When I use TRY_CONVERT, I'm getting the error: "'TRY_CONVERT' is not a recognized built-in function name." – M. McCrary Nov 30 '18 at 19:47
@M.McCrary . . . `TRY_CONVERT()` has been available since SQL Server 2012. Check your compatibility level (https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017). Anything at or over 110 should work. – Gordon Linoff Nov 30 '18 at 19:51

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

2 Answers2