Converting a string to a datetime in MS SQL Server

Question

select CONVERT(datetime, '12/16/2001 11:00:00 PM', ???)

By looking at https://msdn.microsoft.com/en-us/library/ms187928.aspx , I'm unable to find a valid style that I can use with the Convert function for this type of date. I always get the error 'Conversion failed when converting date and/or time from character string'

Any ideas?

What version of sql server are you using? You do not need to provide a format when converting to Datetime, format is only needed when you are converting datetime to string. Just use `select CONVERT(datetime, '12/16/2001 11:00:00 PM')` this should work fine. — M.Ali, Dec 14 '16 at 15:57
@M.Ali This is wrong! Just try this `select CONVERT(datetime, '01/02/2001 11:00:00 PM', 101),CONVERT(datetime, '01/02/2001 11:00:00 PM', 103)`... The default setting might work fine - or not... — Shnugo, Dec 14 '16 at 16:08
@Shnugo I dont know what you mean by `"it might work!!"`, when you are converting to DateTime value, format parameter is irrelavent, SQL Server store Datetime values in ticks and intergers internally and not as strings, as long as you can convert a value to Datetime it is good enough for SQL Server to hande it, why bother with format anyway :) — M.Ali, Dec 14 '16 at 16:19
@M.Ali the format paramter tells the function **how to interpret** the string literal. If `01/02/2000` is taken as first of February or second of January is depending on your system's settings. One should never rely on this... — Shnugo, Dec 14 '16 at 16:21
@M.Ali Try this `set language english;select CONVERT(datetime, '01/02/2001 11:00:00 PM');` And now set the language to `german` and compare the result... — Shnugo, Dec 14 '16 at 16:29
@Shnugo the third parameter in `Convert()` function is `Style/Format` not `Culture` you are confusing two things here, How a string is handled in sql server depends on the `Culture` (language) settings, what kind/style/format of string the convert function converts to depends on the third parameter(`Style/format`). What ever I have suggested is based on the SQL Server's Default Lanugage settings. — M.Ali, Dec 14 '16 at 16:38
@M.Ali No, I'm not confusing anything! And I know what is *culture* and what is *format*. But the **default format** is bound to the system's culture. Your first comment is simply wrong (but got 3 ups already...). One should never rely on defaults! Therefore calling `CONVERT` on a date literal **without the third parameter** is gambling! — Shnugo, Dec 14 '16 at 16:41
Thanks everybody for helping. Removing the style parameter made the trick. They are running 2008. I ran some queries and seems to work ok. — LEM, Dec 14 '16 at 17:08
@LEM Did you read the comments? *Removing the style parameter made the trick* is something you should really avoid if this is not a *one-time-action*... — Shnugo, Dec 14 '16 at 17:14
@Shnugo Yes, I read them but nobody has said so far here that you are right about that discussion you are having with M.Ali — LEM, Dec 14 '16 at 19:20
@LEM Try it yourself: This works: `set language english;select CONVERT(datetime, '12/16/2001 11:00:00 PM');` But set the language to `german`. The same statement will break (there is no month nr 16). Try it with a date, where day and month both are `<13`. You would not get an error but differing results (what is even worse!). M.Alis advise is just wrong... This might be OK, if you can be absolutely sure, that this piece of code is now (and in future) running on enlish servers only... — Shnugo, Dec 14 '16 at 19:49
@LEM Look at this `select * from sys.syslanguages;` Each system language has its date format (dmy, mdy, ymd). How a date literal is interpreted *by default* depends on your system's settings. And this is something you should **really never** rely on... — Shnugo, Dec 14 '16 at 19:51
@Shnugo. Ok, I'll follow your advice. Thanks for providing all that information. — LEM, Dec 14 '16 at 20:48

score 1 · Accepted Answer · edited May 23 '17 at 11:53

CONVERT needs the appropriate parameter to know how to parse a date string.

Look at this link to find details.

Your case needs 101:

select CONVERT(datetime, '12/16/2001 11:00:00 PM', 101)

If this is under your control, you should avoid any culture related date literal. Best for DateTime - if you have to type in a date with time - is ODBC

SELECT {ts'2001-12-16 23:00:00'};

Or ISO 8601

SELECT CONVERT(DATETIME,'2001-12-16T23:00:00',126);

Here's a related answer

And here's another one

score 1 · Answer 2 · answered Jun 07 '20 at 02:50

You don't need to find the correct format in most cases. SQL Server is remarkable in it's ability to figure out most renditions of things that look like a DATETIME. I'm pretty sure you won't find a format number for most of the conversions in the example code below but ALL of the formats but two do a correct conversion with DATETIME without any format numbering at all.

In the same code, you'll also see how badly DATETIME2 was implemented. If you need a reason for DATETIME to not go away in favor of DATETIME2, this is a major reason!

--===== Set the language for this example.
    SET LANGUAGE ENGLISH --Same a US-English
;
--===== Use a table constructor as if it were a table for this example.
 SELECT *
        ,DateTimeCONVERT  = TRY_CONVERT(DATETIME,StringDT)
        ,DateTimeCAST     = TRY_CAST(StringDT AS DATETIME)
        ,DateTime2CONVERT = TRY_CONVERT(DATETIME2,StringDT)
        ,DateTime2CAST    = TRY_CAST(StringDT AS DATETIME2)
   FROM (
         VALUES
         ('Same Format As In The OP'    ,'12/16/2001 01:51:01 PM')
        ,('Almost Normal'               ,'16 December, 2001 1:51:01 PM')
        ,('More Normal'                 ,'December 16, 2001 01:51:01 PM')
        ,('Time Up Front + Spaces'      ,'   13:51:01  16 December   2001')
        ,('Totally Whacky Format #01'   ,'  16  13:51:01  December   2001')
        ,('Totally Whacky Format #02'   ,'  16    December 13:51:01  2001  ')
        ,('Totally Whacky Format #03'   ,'  16    December 01:51:01  PM 2001  ')
        ,('Totally Whacky Format #04'   ,' 2001 16    December 01:51:01  PM ')
        ,('Totally Whacky Format #05'   ,' 2001    December 01:51:01  PM  16  ')
        ,('Totally Whacky Format #06'   ,' 2001 16    December  01:51:01 PM  ')
        ,('Totally Whacky Format #07'   ,' 2001 16    December  13:51:01 PM  ')
        ,('Totally Whacky Format #08'   ,' 2001 16  13:51:01 PM  December    ')
        ,('Totally Whacky Format #09'   ,'   13:51:01   PM  2001.12/16 ')
        ,('Totally Whacky Format #10'   ,'   13:51:01   PM  2001.December/16 ')
        ,('Totally Whacky Format #11'   ,'   13:51:01   PM  2001.Dec/16 ')
        ,('Totally Whacky Format #12'   ,'   13:51:01   PM  2001.Dec.16 ')
        ,('Totally Whacky Format #13'   ,'   13:51:01   PM  2001/Dec.16')
        ,('Totally Whacky Format #14'   ,'   13:51:01   PM  2001 . 12/16 ')
        ,('Totally Whacky Format #15'   ,'   13:51:01   PM  2001 . December / 16 ')
        ,('Totally Whacky Format #16'   ,'   13:51:01   PM  2001 . Dec /   16 ')
        ,('Totally Whacky Format #17'   ,'   13:51:01   PM  2001 . Dec .   16 ')
        ,('Totally Whacky Format #18'   ,'   13:51:01   PM  2001 / Dec .   16')
        ,('Totally Whacky Format #19'   ,'   13:51:01   PM  2001 . Dec -   16 ')
        ,('Totally Whacky Format #20'   ,'   13:51:01   PM  2001 - Dec -   16 ')
        ,('Totally Whacky Format #21'   ,'   13:51:01   PM  2001 - Dec .   16')
        ,('Totally Whacky Format #22'   ,'   13:51:01   PM  2001 - Dec /   16 ')
        ,('Totally Whacky Format #23'   ,'   13:51:01   PM  2001 / Dec -   16')
        ,('Just the year'               ,' 2001      ')
        ,('YYYYMM'                      ,' 200112      ')
        ,('YYYY MMM'                    ,'2001 Dec')
        ,('YYYY-MMM'                    ,'2001-Dec')
        ,('YYYY    .     MMM'           ,'2001    .     Dec')
        ,('YYYY    /     MMM'           ,'2001    /     Dec')
        ,('YYYY    -     MMM'           ,'2001    /     Dec')
        ,('Forgot The Spaces #1'        ,'2001December26')
        ,('Forgot The Spaces #2'        ,'2001Dec26')
        ,('Forgot The Spaces #3'        ,'26December2001')
        ,('Forgot The Spaces #4'        ,'26Dec2001')
        ,('Forgot The Spaces #5'        ,'26Dec2001 13:51:01')
        ,('Forgot The Spaces #6'        ,'26Dec2001 13:51:01PM')
        ,('Oddly, this doesn''t work'   ,'2001-12')
        ,('Oddly, this doesn''t work'   ,'12-2001')
        ) v (Description,StringDT)
;

I can't post a big enough graphic to get all the results in so just run the code to see what I'm talking about.

Converting a string to a datetime in MS SQL Server

2 Answers2

Linked