date parsing

Hello,

I have a source with two smalldatetime fields, the first field contains 8/1/2006 12:00:00 AM, and the second field contains 8/10/2006 7:57:00 PM.

I would like to have the date from the first field and the time from the second field. No chance of changing the source system to do this for me.

What I have so far works, except the time portion is converted to 19:57:00 instead of 7:57:00 p.m. Any Ideas? My expression is below.

(DT_STR,2,1252)DATEPART("month",FIELD1) + "/" + (DT_STR,2,1252)DATEPART("Day",FIELD1) + "/" + (DT_STR,4,1252)DATEPART("Year",FIELD1) + " " + (DT_STR,2,1252)DATEPART("Hour",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("Minute",FIELD2) + ":" + (DT_STR,2,1252)DATEPART("SS",FIELD2)

Thanks!

Try casting the values as DT_DBTIME or DT_DBDATE.

-Jamie

|||I added a data conversion transform to convert the output from the derived column transform to a database timestamp and it appears to be working. I probably could do all of this in one transformation, but this will work for now. Thanks!|||

You could wrap the cast to DT_DBTIMESTAMP around your whole expression in the derived column to avoid using the data convert downstream.

Mark

|||Thanks. I tried that and it worked great.