Datetime conversion

• monilps

  SSCommitted

  Points: 1686

  More actions

  January 29, 2016 at 9:54 am

  #308106

  Hi,

  I need to convert varchar datatype "14-Apr" into datetime datatype "2014-04-01 00:00:00.000"

  Thanks in advance.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 29, 2016 at 10:00 am

  #1855296

  Have a look at the CAST and CONVERT topic in Books Online. You may have to do a bit of manipulation on your string, for example adding a year on to it. Do you always want to return a date in 2014?

  John

• monilps

  SSCommitted

  Points: 1686

  More actions

  January 29, 2016 at 10:04 am

  #1855298

  I looked at the CAST and CONVERT online but was not able to find the solution.

  It would be always "YY-MON" (MON=First three letters of the month).

  In meantime, I am still researching it.

  Thanks.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 29, 2016 at 10:04 am

  #1855300

  not sure but maybe?

  SELECT TRY_CONVERT(datetime, '20'+'14-Apr') AS Result;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 29, 2016 at 10:20 am

  #1855306

  Maybe this:

  DECLARE @date varchar(10) = '14-Apr'

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 29, 2016 at 12:26 pm

  #1855349

  Luis Cazares (1/29/2016)

  ---

  Maybe this:

  DECLARE @date varchar(10) = '14-Apr'

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

  Hi Luis......why not just use this instead??

  SELECT CONVERT(datetime, '20'+@date)

  ??

  also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 29, 2016 at 12:31 pm

  #1855353

  J Livingston SQL (1/29/2016)

  ---

  Luis Cazares (1/29/2016)

  ---

  Maybe this:

  DECLARE @date varchar(10) = '14-Apr'

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

  Hi Luis......why not just use this instead??

  SELECT CONVERT(datetime, '20'+@date)

  ??

  also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error

  I'm still afraid of previous century. :hehe:

  DECLARE @date varchar(10) = '99-Apr';

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6),

  CONVERT(datetime, '20'+@date) ;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 29, 2016 at 12:36 pm

  #1855355

  Luis Cazares (1/29/2016)

  ---

  J Livingston SQL (1/29/2016)

  ---

  Luis Cazares (1/29/2016)

  ---

  Maybe this:

  DECLARE @date varchar(10) = '14-Apr'

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6)

  Hi Luis......why not just use this instead??

  SELECT CONVERT(datetime, '20'+@date)

  ??

  also,I believe that TRY_CONVERT (2012+) will throw a NULL rather than an error

  I'm still afraid of previous century. :hehe:

  DECLARE @date varchar(10) = '99-Apr';

  SELECT CONVERT(datetime, '01 ' + RIGHT(@date,3) + ' ' + LEFT(@date,2), 6),

  CONVERT(datetime, '20'+@date) ;

  fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)

  Thanks

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 29, 2016 at 12:45 pm

  #1855358

  J Livingston SQL (1/29/2016)

  ---

  fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)

  Thanks

  Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  January 29, 2016 at 5:08 pm

  #1855419

  Since the final date format is "self delimiting", you don't have to add spaces in the result. But I would adjust the code to allow the day to be only a single digit, Edit:and to delimit the day by any nonnumeric char, not just '-':

  SELECT CONVERT(datetime, '1' + RIGHT(date,3) + LEFT(date,CHARINDEX('-',date)-1), 6) --Edit

  SELECT CONVERT(datetime, '1' + RIGHT(date,3) + LEFT(date,PATINDEX('%[^0-9]%',date)-1), 6) --Edit

  FROM (

  SELECT '99-Apr' AS date UNION ALL

  SELECT '14-Aug' UNION ALL

  SELECT '2-Sep' UNION ALL

  SELECT '5Dec' --Edit

  ) AS test_data

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Ed Wagner

  SSC Guru

  Points: 287024

  More actions

  January 29, 2016 at 5:23 pm

  #1855422

  Luis Cazares (1/29/2016)

  ---

  J Livingston SQL (1/29/2016)

  ---

  fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)

  Thanks

  Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).

  The TRY_CONVERT should also handle an invalid month string without throwing an error. Since the column is probably a varchar, you can't be sure of what'll be in there.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  January 29, 2016 at 6:07 pm

  #1855428

  Ed Wagner (1/29/2016)

  ---

  Luis Cazares (1/29/2016)

  ---

  J Livingston SQL (1/29/2016)

  ---

  fair enough...see your (very valid) point. but why use "CONVERT" instead of "TRY_CONVERT"? (2012 forum)

  Thanks

  Bad practice. I didn't see it was a 2012 forum and I'm used on getting restricted to 2008. Fortunately, that's changing. Also, the syntax is the same, so hopefully the OP will choose the best option (TRY_CONVERT).

  The TRY_CONVERT should also handle an invalid month string without throwing an error. Since the column is probably a varchar, you can't be sure of what'll be in there.

  In such a case, I'd likely want it to throw an error.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)