Casting string as int in derived column

Question

Casting string as int in derived column

david_h_edmonds

Default port

Points: 1430
More actions
August 16, 2018 at 5:55 am

#376196

Hello, I am doing some datetime manipulations in an ssis derived column and as part of this, I need to convert 12 hr to 24hr time. The times are marked AM or PM so no issue identifying them however the cast part of the expression to convert the string_hour back to int so I can add 12 to it for PM times is not working.
Can anyone see what I need to do please?
[String_AM_PM] == "AM" ? [String_Hour] + ":" + [String_Minute] + ":" + [String_second] : (DT_UI4) [String_Hour] + ":" + [String_Minute] + ":" + [String_second]
Cheers
Dave

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

david_h_edmonds - Thursday, August 16, 2018 5:55 AM
Hello, I am doing some datetime manipulations in an ssis derived column and as part of this, I need to convert 12 hr to 24hr time. The times are marked AM or PM so no issue identifying them however the cast part of the expression to convert the string_hour back to int so I can add 12 to it for PM times is not working.
Can anyone see what I need to do please?
[String_AM_PM] == "AM" ? [String_Hour] + ":" + [String_Minute] + ":" + [String_second] : (DT_UI4) [String_Hour] + ":" + [String_Minute] + ":" + [String_second]
Cheers
Dave

Something like this should work for the 'adding 12 hours' part:

(DT_STR, 2, 1252) ((DT_I4) [String_Hour] +12) + ":" + [String_Minute] + ":" + [String_Second]

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

david_h_edmonds Default port Points: 1430 More actions · Answer 2

Phil Parkin - Thursday, August 16, 2018 6:33 AM
david_h_edmonds - Thursday, August 16, 2018 5:55 AM
Hello, I am doing some datetime manipulations in an ssis derived column and as part of this, I need to convert 12 hr to 24hr time. The times are marked AM or PM so no issue identifying them however the cast part of the expression to convert the string_hour back to int so I can add 12 to it for PM times is not working.
Can anyone see what I need to do please?
[String_AM_PM] == "AM" ? [String_Hour] + ":" + [String_Minute] + ":" + [String_second] : (DT_UI4) [String_Hour] + ":" + [String_Minute] + ":" + [String_second]
Cheers
Dave
Something like this should work for the 'adding 12 hours' part:
(DT_STR, 2, 1252) ((DT_I4) [String_Hour] +12) + ":" + [String_Minute] + ":" + [String_Second]

Perfect as always.

Thanks so much.

Final package ended up as 5 levels of derived columns each relying on the previous level.
Had to remember to treat midnight as 0 and midday as 12 still to make it work though.

Dave