CODEWITHSUNDEEP
sqlsql-servert-sql
fiqbal
fiqbal

Reputation: 61

Error when converting mm/dd/yyyy string to datetime

I am trying to update a table and I am only given a varchar value:

UPDATE table
SET Deadline = CONVERT(DATETIME, '06/18/2012 12:00 AM')
FROM table
WHERE nominal_id = 2650

The error I get is:

Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string.

But when I do a simple SELECT CONVERT(DATETIME, '12:00 PM'), it returns the proper value. What do I seem to be missing here? Should I be CASTing the varchar?

Upvotes: 2

Views: 2701

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

You should always use unambiguous, region-agnostic strings for date/time literals. In your case this should work better (and you won't need an explicit convert to datetime):

'20120618 12:00 AM'

I highly recommend you change the input format. If you can't change the input format, then perhaps:

CONVERT(DATETIME, CONVERT(VARCHAR(19), '06/18/2012 12:00 AM', 101))

Upvotes: 6

Related Questions