Converting varchar to datetime

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  March 14, 2012 at 3:09 pm

  #1459512

  ColdCoffee (3/14/2012)

  ---

  How about this?

  Sample data

  DECLARE @Table TABLE ( DatetimeasVC VARCHAR(30) );

  INSERT @Table

  SELECT '10/28/2011 11:47:55.686455 AM'

  UNION ALL

  SELECT '9/28/2011 11:47:55.123455 AM'

  UNION ALL

  SELECT '11/12/2011 11:47:55.789455 PM'

  UNION ALL

  SELECT '10/9/2011 11:47:55.9996 AM'

  UNION ALL

  SELECT '1/26/2011 11:47:55.3456 PM'

  UNION ALL

  SELECT '1/2/2012 11:47:55.2334455 PM'

  And the code:

  SELECT DatetimeasVC , CrsApp1.String , CONVERT( DATETIME , CrsApp1.String) [Converted To DateTime]

  FROM @Table

  CROSS APPLY (SELECT CHARINDEX('.',DatetimeasVC ) ) CrsApp (Idx)

  CROSS APPLY (SELECT LEFT ( DatetimeasVC , CrsApp.Idx + 3 ) + ' ' + RIGHT( DatetimeasVC , 2) ) CrsApp1 (String)

  Looks good to me. Now we just need to hear from the OP.

• drew.allen

  SSC Guru

  Points: 76776

  More actions

  March 15, 2012 at 7:27 am

  #1459826

  ColdCoffee (3/14/2012)

  ---

  How about this?

  SELECT DatetimeasVC , CrsApp1.String , CONVERT( DATETIME , CrsApp1.String) [Converted To DateTime]

  FROM @Table

  CROSS APPLY (SELECT CHARINDEX('.',DatetimeasVC ) ) CrsApp (Idx)

  CROSS APPLY (SELECT LEFT ( DatetimeasVC , CrsApp.Idx + 3 ) + ' ' + RIGHT( DatetimeasVC , 2) ) CrsApp1 (String)

  This fails when the string has zero or one decimal place(s). This code will work for those formats.

  SELECT DatetimeasVC , CrsApp1.String , CONVERT( DATETIME , CrsApp1.String) [Converted To DateTime]

  FROM @Table

  CROSS APPLY (SELECT PATINDEX('%.[0-9][0-9]%',DatetimeasVC ) ) CrsApp (Idx)

  CROSS APPLY (SELECT CASE WHEN CrsApp.Idx > 0 THEN LEFT ( DatetimeasVC , CrsApp.Idx + 3 ) + ' ' + RIGHT( DatetimeasVC , 2) ELSE DatetimeasVC END ) AS CrsApp1 (String)

  I replaced the CHARINDEX() with a PATINDEX() to make sure there were at least two digits after the decimal point and then used a CASE expression to return the original string if when there was no match for the PATINDEX().

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA