Sql Server string to date conversion

Question

I want to convert a string like this    10 15 2008 10 06 32 PM    into the equivalent DATETIME value in Sql Server   In Oracle  I would say this   TO DATE  10 15 2008 10 06 32 PM   MM DD YYYY HH MI SS AM     This question implies that I must parse the string into one of the standard formats  and then convert using one of those codes   That seems ludicrous for such a mundane operation   Is there an easier way

User · Answer

I know this is a wicked old post with a whole lot of answers but a lot of people think that they NEED to either break things apart and put them back together or they insist that there's no way to implicitly do the conversion the OP original asked for.

To review and to hopefully provide an easy answer to others with the same question, the OP asked how to convert '10/15/2008 10:06:32 PM' to a DATETIME. Now, SQL Server does have some language dependencies for temporal conversions but if the language is english or something similar, this becomes a simple problem... just do the conversion and don't worry about the format. For example (and you can use CONVERT or CAST)...

 SELECT UsingCONVERT = CONVERT(DATETIME,'10/15/2008 10:06:32 PM')
        ,UsingCAST   = CAST('10/15/2008 10:06:32 PM' AS DATETIME)
;

... and that produces the follow answers, both of which are correct.

Like they say on the TV commercials, "But wait! Don't order yet! For no extra cost, it can do MUCH more!"

Let's see the real power of temporal conversions with the DATETIME and partially examine the mistake known as DATETIME2. Check out the whacky formats that DATETIME can handle auto-magically and that DATETIME2 cannot. Run the following code and see...

--===== 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)
;

So, yeah... SQL Server DOES actually have a pretty flexible method of handling all sorts of weird-o temporal formats and no special handling is required. We didn't even need to remove the "PM"s that were added to the 24 hour times. It's "PFM" (Pure Freakin' Magic).

Things will vary a bit depending on the the LANGUAGE is that you've selected for your server but a whole lot of it will be handled either way.

And these "auto-magic" conversions aren't something new. They go a real long way back.

User · Answer

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime Parse or ParseExact function to return the DateTime value with a specified format

User · Answer

Use this   SELECT convert datetime   2018-10-25 20 44 11 500   121  -- yyyy-mm-dd hh mm ss mmm   And refer to the table in the official documentation for the conversion codes

User · Answer

SQL Server  2005  2000  7 0  does not have any flexible  or even non-flexible  way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type   By  arbitrarily   I mean  a form that the person who wrote it  though perhaps not you or I or someone on the other side of the planet  would consider to be intuitive and completely obvious    Frankly  I m not sure there is any such algorithm

User · Answer

If you want SQL Server to try and figure it out  just use CAST CAST  whatever  AS datetime  However that is a bad idea in general   There are issues with international dates that would come up   So as you ve found  to avoid those issues  you want to use the ODBC canonical format of the date   That is format number 120  20 is the format for just two digit years  I don t think SQL Server has a built-in function that allows you to provide a user given format   You can write your own and might even find one if you search online

User · Answer

This page has some references for all of the specified datetime conversions available to the CONVERT function   If your values don t fall into one of the acceptable patterns  then I think the best thing is to go the ParseExact route

User · Answer

You can easily achieve this by using this code  SELECT Convert datetime  Convert varchar 30   10 15 2008 10 06 32 PM  102  102

User · Answer

This page has some references for all of the specified datetime conversions available to the CONVERT function   If your values don t fall into one of the acceptable patterns  then I think the best thing is to go the ParseExact route

User · Answer

dateadd day 0  10 15 2008 10 06 32 PM

User · Answer

Run this through your query processor   It formats dates and or times like so and one of these should give you what you re looking for   It wont be hard to adapt     Declare  d datetime select  d   getdate    select  d as OriginalDate  convert varchar  d 100  as ConvertedDate  100 as FormatValue   mon dd yyyy hh miAM  or PM   as OutputFormat union all select  d convert varchar  d 101  101  mm dd yy  union all select  d convert varchar  d 102  102  yy mm dd  union all select  d convert varchar  d 103  103  dd mm yy  union all select  d convert varchar  d 104  104  dd mm yy  union all select  d convert varchar  d 105  105  dd-mm-yy  union all select  d convert varchar  d 106  106  dd mon yy  union all select  d convert varchar  d 107  107  Mon dd  yy  union all select  d convert varchar  d 108  108  hh mm ss  union all select  d convert varchar  d 109  109  mon dd yyyy hh mi ss mmmAM  or PM   union all select  d convert varchar  d 110  110  mm-dd-yy  union all select  d convert varchar  d 111  111  yy mm dd  union all select  d convert varchar  d 12  12  yymmdd  union all select  d convert varchar  d 112  112  yyyymmdd  union all select  d convert varchar  d 113  113  dd mon yyyy hh mm ss mmm 24h   union all select  d convert varchar  d 114  114  hh mi ss mmm 24h   union all select  d convert varchar  d 120  120  yyyy-mm-dd hh mi ss 24h   union all select  d convert varchar  d 121  121  yyyy-mm-dd hh mi ss mmm 24h   union all select  d convert varchar  d 126  126  yyyy-mm-dd Thh mm ss mmm no spaces

User · Answer

Run this through your query processor   It formats dates and or times like so and one of these should give you what you re looking for   It wont be hard to adapt     Declare  d datetime select  d   getdate    select  d as OriginalDate  convert varchar  d 100  as ConvertedDate  100 as FormatValue   mon dd yyyy hh miAM  or PM   as OutputFormat union all select  d convert varchar  d 101  101  mm dd yy  union all select  d convert varchar  d 102  102  yy mm dd  union all select  d convert varchar  d 103  103  dd mm yy  union all select  d convert varchar  d 104  104  dd mm yy  union all select  d convert varchar  d 105  105  dd-mm-yy  union all select  d convert varchar  d 106  106  dd mon yy  union all select  d convert varchar  d 107  107  Mon dd  yy  union all select  d convert varchar  d 108  108  hh mm ss  union all select  d convert varchar  d 109  109  mon dd yyyy hh mi ss mmmAM  or PM   union all select  d convert varchar  d 110  110  mm-dd-yy  union all select  d convert varchar  d 111  111  yy mm dd  union all select  d convert varchar  d 12  12  yymmdd  union all select  d convert varchar  d 112  112  yyyymmdd  union all select  d convert varchar  d 113  113  dd mon yyyy hh mm ss mmm 24h   union all select  d convert varchar  d 114  114  hh mi ss mmm 24h   union all select  d convert varchar  d 120  120  yyyy-mm-dd hh mi ss 24h   union all select  d convert varchar  d 121  121  yyyy-mm-dd hh mi ss mmm 24h   union all select  d convert varchar  d 126  126  yyyy-mm-dd Thh mm ss mmm no spaces

User · Answer

In SQL Server Denali  you will be able to do something that approaches what you re looking for  But you still can t just pass any arbitrarily defined wacky date string and expect SQL Server to accommodate  Here is one example using something you posted in your own answer  The FORMAT   function and can also accept locales as an optional argument - it is based on  Net s format  so most if not all of the token formats you d expect to see will be there   DECLARE  d DATETIME    2008-10-13 18 45 19    -- returns Oct-13 2008 18 45 19  SELECT FORMAT  d  N MMM-dd yyyy HH mm ss     -- returns NULL if the conversion fails  SELECT TRY PARSE FORMAT  d  N MMM-dd yyyy HH mm ss   AS DATETIME    -- returns an error if the conversion fails  SELECT PARSE FORMAT  d  N MMM-dd yyyy HH mm ss   AS DATETIME     I strongly encourage you to take more control and sanitize your date inputs  The days of letting people type dates using whatever format they want into a freetext form field should be way behind us by now  If someone enters 8 9 2011 is that August 9th or September 8th  If you make them pick a date on a calendar control  then the app can control the format  No matter how much you try to predict your users  behavior  they ll always figure out a dumber way to enter a date that you didn t plan for   Until Denali  though  I think that  Ovidiu has the best advice so far    this can be made fairly trivial by implementing your own CLR function  Then you can write a case switch for as many wacky non-standard formats as you want     UPDATE for  dhergert   SELECT TRY PARSE  10 15 2008 10 06 32 PM  AS DATETIME USING  en-us    SELECT TRY PARSE  15 10 2008 10 06 32 PM  AS DATETIME USING  en-gb      Results   2008-10-15 22 06 32 000 2008-10-15 22 06 32 000   You still need to have that other crucial piece of information first  You can t use native T-SQL to determine whether 6 9 2012 is June 9th or September 6th

User · Answer

Run this through your query processor   It formats dates and or times like so and one of these should give you what you re looking for   It wont be hard to adapt     Declare  d datetime select  d   getdate    select  d as OriginalDate  convert varchar  d 100  as ConvertedDate  100 as FormatValue   mon dd yyyy hh miAM  or PM   as OutputFormat union all select  d convert varchar  d 101  101  mm dd yy  union all select  d convert varchar  d 102  102  yy mm dd  union all select  d convert varchar  d 103  103  dd mm yy  union all select  d convert varchar  d 104  104  dd mm yy  union all select  d convert varchar  d 105  105  dd-mm-yy  union all select  d convert varchar  d 106  106  dd mon yy  union all select  d convert varchar  d 107  107  Mon dd  yy  union all select  d convert varchar  d 108  108  hh mm ss  union all select  d convert varchar  d 109  109  mon dd yyyy hh mi ss mmmAM  or PM   union all select  d convert varchar  d 110  110  mm-dd-yy  union all select  d convert varchar  d 111  111  yy mm dd  union all select  d convert varchar  d 12  12  yymmdd  union all select  d convert varchar  d 112  112  yyyymmdd  union all select  d convert varchar  d 113  113  dd mon yyyy hh mm ss mmm 24h   union all select  d convert varchar  d 114  114  hh mi ss mmm 24h   union all select  d convert varchar  d 120  120  yyyy-mm-dd hh mi ss 24h   union all select  d convert varchar  d 121  121  yyyy-mm-dd hh mi ss mmm 24h   union all select  d convert varchar  d 126  126  yyyy-mm-dd Thh mm ss mmm no spaces

User · Answer

Run this through your query processor   It formats dates and or times like so and one of these should give you what you re looking for   It wont be hard to adapt     Declare  d datetime select  d   getdate    select  d as OriginalDate  convert varchar  d 100  as ConvertedDate  100 as FormatValue   mon dd yyyy hh miAM  or PM   as OutputFormat union all select  d convert varchar  d 101  101  mm dd yy  union all select  d convert varchar  d 102  102  yy mm dd  union all select  d convert varchar  d 103  103  dd mm yy  union all select  d convert varchar  d 104  104  dd mm yy  union all select  d convert varchar  d 105  105  dd-mm-yy  union all select  d convert varchar  d 106  106  dd mon yy  union all select  d convert varchar  d 107  107  Mon dd  yy  union all select  d convert varchar  d 108  108  hh mm ss  union all select  d convert varchar  d 109  109  mon dd yyyy hh mi ss mmmAM  or PM   union all select  d convert varchar  d 110  110  mm-dd-yy  union all select  d convert varchar  d 111  111  yy mm dd  union all select  d convert varchar  d 12  12  yymmdd  union all select  d convert varchar  d 112  112  yyyymmdd  union all select  d convert varchar  d 113  113  dd mon yyyy hh mm ss mmm 24h   union all select  d convert varchar  d 114  114  hh mi ss mmm 24h   union all select  d convert varchar  d 120  120  yyyy-mm-dd hh mi ss 24h   union all select  d convert varchar  d 121  121  yyyy-mm-dd hh mi ss mmm 24h   union all select  d convert varchar  d 126  126  yyyy-mm-dd Thh mm ss mmm no spaces

User · Answer

Personally if your dealing with arbitrary or totally off the wall formats  provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date datetime component

User · Answer

SQL Server  2005  2000  7 0  does not have any flexible  or even non-flexible  way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type   By  arbitrarily   I mean  a form that the person who wrote it  though perhaps not you or I or someone on the other side of the planet  would consider to be intuitive and completely obvious    Frankly  I m not sure there is any such algorithm

User · Answer

Took me a minute to figure this out so here it is in case it might help someone   In SQL Server 2012 and better you can use this function   SELECT DATEFROMPARTS 2013  8  19     Here s how I ended up extracting the parts of the date to put into this function   select DATEFROMPARTS right cms projectedInstallDate 4  left cms ProjectedInstallDate 2  right  left cms ProjectedInstallDate 5  2   as  dateFromParts  from MyTable

User · Answer

You can easily achieve this by using this code  SELECT Convert datetime  Convert varchar 30   10 15 2008 10 06 32 PM  102  102

User · Answer

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime Parse or ParseExact function to return the DateTime value with a specified format

User · Answer

convert string to datetime in MSSQL implicitly  create table tmp      ENTRYDATETIME datetime     insert into tmp  ENTRYDATETIME  values  getdate     insert into tmp  ENTRYDATETIME  values   20190101     --convert string  yyyymmdd  to datetime   select   from tmp where ENTRYDATETIME  gt   20190925   --yyyymmdd  select   from tmp where ENTRYDATETIME  gt   20190925 12 11 09 555 --yyyymmdd HH MIN SS MS

User · Answer

convert string to datetime in MSSQL implicitly  create table tmp      ENTRYDATETIME datetime     insert into tmp  ENTRYDATETIME  values  getdate     insert into tmp  ENTRYDATETIME  values   20190101     --convert string  yyyymmdd  to datetime   select   from tmp where ENTRYDATETIME  gt   20190925   --yyyymmdd  select   from tmp where ENTRYDATETIME  gt   20190925 12 11 09 555 --yyyymmdd HH MIN SS MS

User · Answer

dateadd day 0  10 15 2008 10 06 32 PM

User · Answer

why not try   select convert date  10 15 2011 00 00 00  104  as  MM dd YYYY    date formats can be found at SQL Server Helper   SQL Server Date Formats

User · Answer

Try this  Cast  7 7 2011  as datetime    and  Convert varchar 30   7 7 2011  102    See CAST and CONVERT  Transact-SQL  for more details

User · Answer

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime Parse or ParseExact function to return the DateTime value with a specified format

User · Answer

This page has some references for all of the specified datetime conversions available to the CONVERT function   If your values don t fall into one of the acceptable patterns  then I think the best thing is to go the ParseExact route

User · Answer

In SQL Server Denali  you will be able to do something that approaches what you re looking for  But you still can t just pass any arbitrarily defined wacky date string and expect SQL Server to accommodate  Here is one example using something you posted in your own answer  The FORMAT   function and can also accept locales as an optional argument - it is based on  Net s format  so most if not all of the token formats you d expect to see will be there   DECLARE  d DATETIME    2008-10-13 18 45 19    -- returns Oct-13 2008 18 45 19  SELECT FORMAT  d  N MMM-dd yyyy HH mm ss     -- returns NULL if the conversion fails  SELECT TRY PARSE FORMAT  d  N MMM-dd yyyy HH mm ss   AS DATETIME    -- returns an error if the conversion fails  SELECT PARSE FORMAT  d  N MMM-dd yyyy HH mm ss   AS DATETIME     I strongly encourage you to take more control and sanitize your date inputs  The days of letting people type dates using whatever format they want into a freetext form field should be way behind us by now  If someone enters 8 9 2011 is that August 9th or September 8th  If you make them pick a date on a calendar control  then the app can control the format  No matter how much you try to predict your users  behavior  they ll always figure out a dumber way to enter a date that you didn t plan for   Until Denali  though  I think that  Ovidiu has the best advice so far    this can be made fairly trivial by implementing your own CLR function  Then you can write a case switch for as many wacky non-standard formats as you want     UPDATE for  dhergert   SELECT TRY PARSE  10 15 2008 10 06 32 PM  AS DATETIME USING  en-us    SELECT TRY PARSE  15 10 2008 10 06 32 PM  AS DATETIME USING  en-gb      Results   2008-10-15 22 06 32 000 2008-10-15 22 06 32 000   You still need to have that other crucial piece of information first  You can t use native T-SQL to determine whether 6 9 2012 is June 9th or September 6th

User · Answer

why not try   select convert date  10 15 2011 00 00 00  104  as  MM dd YYYY    date formats can be found at SQL Server Helper   SQL Server Date Formats

User · Answer

Try this  Cast  7 7 2011  as datetime    and  Convert varchar 30   7 7 2011  102    See CAST and CONVERT  Transact-SQL  for more details

User · Answer

Personally if your dealing with arbitrary or totally off the wall formats  provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date datetime component

User · Answer

Took me a minute to figure this out so here it is in case it might help someone   In SQL Server 2012 and better you can use this function   SELECT DATEFROMPARTS 2013  8  19     Here s how I ended up extracting the parts of the date to put into this function   select DATEFROMPARTS right cms projectedInstallDate 4  left cms ProjectedInstallDate 2  right  left cms ProjectedInstallDate 5  2   as  dateFromParts  from MyTable

User · Answer

Use this   SELECT convert datetime   2018-10-25 20 44 11 500   121  -- yyyy-mm-dd hh mm ss mmm   And refer to the table in the official documentation for the conversion codes

User · Answer

I know this is a wicked old post with a whole lot of answers but a lot of people think that they NEED to either break things apart and put them back together or they insist that there's no way to implicitly do the conversion the OP original asked for.

To review and to hopefully provide an easy answer to others with the same question, the OP asked how to convert '10/15/2008 10:06:32 PM' to a DATETIME. Now, SQL Server does have some language dependencies for temporal conversions but if the language is english or something similar, this becomes a simple problem... just do the conversion and don't worry about the format. For example (and you can use CONVERT or CAST)...

 SELECT UsingCONVERT = CONVERT(DATETIME,'10/15/2008 10:06:32 PM')
        ,UsingCAST   = CAST('10/15/2008 10:06:32 PM' AS DATETIME)
;

... and that produces the follow answers, both of which are correct.

Like they say on the TV commercials, "But wait! Don't order yet! For no extra cost, it can do MUCH more!"

Let's see the real power of temporal conversions with the DATETIME and partially examine the mistake known as DATETIME2. Check out the whacky formats that DATETIME can handle auto-magically and that DATETIME2 cannot. Run the following code and see...

--===== 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)
;

So, yeah... SQL Server DOES actually have a pretty flexible method of handling all sorts of weird-o temporal formats and no special handling is required. We didn't even need to remove the "PM"s that were added to the 24 hour times. It's "PFM" (Pure Freakin' Magic).

Things will vary a bit depending on the the LANGUAGE is that you've selected for your server but a whole lot of it will be handled either way.

And these "auto-magic" conversions aren't something new. They go a real long way back.

User · Answer

If you want SQL Server to try and figure it out  just use CAST CAST  whatever  AS datetime  However that is a bad idea in general   There are issues with international dates that would come up   So as you ve found  to avoid those issues  you want to use the ODBC canonical format of the date   That is format number 120  20 is the format for just two digit years  I don t think SQL Server has a built-in function that allows you to provide a user given format   You can write your own and might even find one if you search online

User · Answer

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime Parse or ParseExact function to return the DateTime value with a specified format

User · Answer

SQL Server  2005  2000  7 0  does not have any flexible  or even non-flexible  way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type   By  arbitrarily   I mean  a form that the person who wrote it  though perhaps not you or I or someone on the other side of the planet  would consider to be intuitive and completely obvious    Frankly  I m not sure there is any such algorithm

User · Answer

If you want SQL Server to try and figure it out  just use CAST CAST  whatever  AS datetime  However that is a bad idea in general   There are issues with international dates that would come up   So as you ve found  to avoid those issues  you want to use the ODBC canonical format of the date   That is format number 120  20 is the format for just two digit years  I don t think SQL Server has a built-in function that allows you to provide a user given format   You can write your own and might even find one if you search online

User · Answer

This page has some references for all of the specified datetime conversions available to the CONVERT function   If your values don t fall into one of the acceptable patterns  then I think the best thing is to go the ParseExact route

User · Answer

SQL Server  2005  2000  7 0  does not have any flexible  or even non-flexible  way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type   By  arbitrarily   I mean  a form that the person who wrote it  though perhaps not you or I or someone on the other side of the planet  would consider to be intuitive and completely obvious    Frankly  I m not sure there is any such algorithm

User · Answer

If you want SQL Server to try and figure it out  just use CAST CAST  whatever  AS datetime  However that is a bad idea in general   There are issues with international dates that would come up   So as you ve found  to avoid those issues  you want to use the ODBC canonical format of the date   That is format number 120  20 is the format for just two digit years  I don t think SQL Server has a built-in function that allows you to provide a user given format   You can write your own and might even find one if you search online

[sql-server] Sql Server string to date conversion

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to sql-server-2005

Examples related to string-to-datetime