timestamp

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18175 More actions · Answer 1

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 · Answer 2

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: 245813 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.