timestamp

• kk.86manu

  Ten Centuries

  Points: 1268

  More actions

  December 26, 2011 at 4:13 am

  #250465

  Hi All,

  I have a scenario where i import the data from a text file.I have a column which actually denotes the modified date(Timestamp).In the Derived column i use the expression (DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4)).

  Input:20111221142954304

  Expected Output:2011-12-21 14:29:54:304

  Actual output using the above expression:2011-12-21 00:00:00.000

  Not getting the hours,mins,sec etc using the above expression.

  Please let me know how to get the expected out.

  Your help would be appreciated.

  Thanks

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  December 27, 2011 at 9:13 am

  #1425753

  Try this:

  (DT_DBTIMESTAMP) (SUBSTRING([Column 5], 1, 4) + "-" + SUBSTRING([Column 5], 5, 2) + "-" + SUBSTRING([Column 5], 7, 2) + " " + SUBSTRING([Column 5], 9, 2) + ":" + SUBSTRING([Column 5], 11, 2) + ":" + SUBSTRING([Column 5], 13, 2) + ":" + SUBSTRING([Column 5], 15, 4))

  I put the last substring as ,4 just incase the length of the last part exceeds 3 characters.

• kk.86manu

  Ten Centuries

  Points: 1268

  More actions

  December 27, 2011 at 10:33 pm

  #1425931

  Thanks for your suggestion.

  I have one more problem

  When i use (DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4) + " " + SUBSTRING([Column 5],9,2) + ":" + SUBSTRING([Column 5],11,2) + ":" + SUBSTRING([Column 5],13,2)) i get the following value

  2011-12-21 14:29:54.000

  Getting the value as NULL when i use the whole expression((DT_DBTIMESTAMP)(SUBSTRING([Column 5],5,2) + "-" + SUBSTRING([Column 5],7,2) + "-" + SUBSTRING([Column 5],1,4) + " " + SUBSTRING([Column 5],9,2) + ":" + SUBSTRING([Column 5],11,2) + ":" + SUBSTRING([Column 5],13,2) + "." + SUBSTRING([Column 5],15,3))

  Can u please let me know whether i am missing something here

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  December 28, 2011 at 3:31 am

  #1426005

  Not all millisecond values can be stored by a SQL Server datetime column and perhaps this is causing issues?

  For example

  select CAST('2011-12-21 14:29:54:304' as datetime)

  will return 2011-12-21 14:29:54.303 - showing that your millisecond value cannot be stored at the precision you have defined.

  To test this, try changing your source data to 20111221142954303 and rerunning.

  ---