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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

dateadd day 0  10 15 2008 10 06 32 PM

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

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

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

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

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

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

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

[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