September 27, 2010 at 8:52 am
Hi Guys,
I want to convert time part into Seconds as below in derived column transformation-
0:01:04.50 = 64.50
h:mm:SS.ss
When I do this operation upto seconds part, it works fine. Only error comes when it tries to include fraction of second.
What I did for this is -
(DT_DECIMAL,2)(((DT_I8)SUBSTRING(Field1,1,1) * 3600) + ((DT_I8)SUBSTRING(Field1,3,2) * 60) + ((DT_I8)SUBSTRING(Field1,6,2))) + ((DT_DECIMAL,2)(SUBSTRING(Field1,9,2)) / 100)
If I add upto part (((DT_I8)SUBSTRING(Field1,1,1) * 3600) + ((DT_I8)SUBSTRING(Field1,3,2) * 60) + ((DT_I8)SUBSTRING(Field1,6,2))) then it works fine and gives me seconds as 64 but when I try to add fraction part ((DT_DECIMAL,2)(SUBSTRING(Field1,9,2)) / 100), it shows type case error. I tried to put Numeric and Decimal both for it but not working.
Does any one knows any simple way to calculate total seconds as mentioned above?
:rolleyes:
September 27, 2010 at 8:57 am
Is it possible that one of the values you're running through the data flow doesn't have the same format? Personally I'd look to using the Datepart function to extract the pieces and then do the multiplication relevant to each part to then get the seconds. Using string functions just seems like it could throw errors if the assumptions aren't true for all records.
Steve.
September 29, 2010 at 8:15 am
Well Thanx for the help Steve but that too didn't work for me...
What I did is I created staging table, and on that table I used conversion functions in SQL Script which worked well...!
Regards,
Saseen
:rolleyes:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply