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: 18181 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: 246985 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel