Having Issues With Date Conversion from a String

• mail 41752

  Grasshopper

  Points: 24

  More actions

  October 16, 2014 at 3:21 pm

  #311938

  I have a table (TBarsImport) with the following varchar column defined, which contains a date/timestamp string to microseconds precision (in SQL Server 2008):

  CurrDateTime

  2014-10-14T02:51:10.289412

  I am using a MERGE statement to convert this CurrDateTime string into three fields into another table (TBars), splitting out the Date (CurrDate) and Time(6) (CurrTime) to separate columns, and as a whole Datetime value to a DateTime2(6) column (CurrDateTime):

  MERGE TBars AS TB

  USING TBarsImport AS TBI

  ON 1 = 0

  WHEN NOT MATCHED

  THEN

  INSERT (

  CurrDateTime,

  CurrDate,

  CurrTime

  )

  VALUES (

  CAST((LEFT(TBI.CurrDateTime, 10) + ' ' + RIGHT(RTRIM(LTRIM(TBI.CurrDateTime)), 15)) AS DATETIME2(6)),

  CAST(LEFT(TBI.CurrDateTime, 10) AS DATE),

  CAST(RIGHT(RTRIM(LTRIM(TBI.CurrDateTime)), 15) AS TIME(6))

  );

  GO

  When this runs, here is how the date, time and datetime columns actually convert:

  CurrDateTime

  2014-10-01 02:51:10.289412

  CurrDate

  2014-10-01

  CurrTime

  02:51:10.289412

  Everything works fine except for the date conversion. 2014-10-14 turns into 2014-10-01.

  I have attempted several other ways to do this (including using CONVERT), but this is the one that actually runs without an error and comes the closest to what needs to actually happen.

  Any ideas about how to correct this date conversion situation would be very much appreciated!

  Thanks, in advance.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 16, 2014 at 3:36 pm

  #1752990

  Are you sure that's the problem?

  If I run the following, it gives the correct result:

  SELECT TBI.CurrDateTime,

  CAST((LEFT(TBI.CurrDateTime, 10) + ' ' + RIGHT(RTRIM(LTRIM(TBI.CurrDateTime)), 15)) AS DATETIME2(6)),

  CAST(LEFT(TBI.CurrDateTime, 10) AS DATE),

  CAST(RIGHT(RTRIM(LTRIM(TBI.CurrDateTime)), 15) AS TIME(6))

  FROM (VALUES(CAST( '2014-10-14T02:51:10.289412' AS varchar(30))))TBI(CurrDateTime)

  And the following also gives the correct result:

  SELECT TBI.CurrDateTime,

  CAST( TBI.CurrDateTime AS datetime2(6)),

  CAST( TBI.CurrDateTime AS date),

  CAST( TBI.CurrDateTime AS time)

  FROM (VALUES(CAST( '2014-10-14T02:51:10.289412' AS varchar(30))))TBI(CurrDateTime)

  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

• mail 41752

  Grasshopper

  Points: 24

  More actions

  October 16, 2014 at 4:05 pm

  #1752994

  Hi Luis,

  Thanks for your reply.

  Yes, your code work perfectly fine on my system as well.

  Here are the results I get when I run it:

  CurrDateTime

  2014-10-14T02:51:10.289412

  2014-10-14 02:51:10.289412

  2014-10-14

  02:51:10.289412

  The problem is that when I use the MERGE statement, using that same data that is in my table (not defined as a literal string), the day converts to 01 vs. 14 as it should. Very strange.

  I appreciate your input, however!

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 16, 2014 at 4:15 pm

  #1752995

  Could you post DDL for tables and sample data that will replicate the issue?

  Table and column names can be changed if needed.

  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

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  October 16, 2014 at 4:23 pm

  #1752999

  Did you check that the string does not have a space at the start, which would lead to a value of 2014-10-1 begin converted...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]