Problems with Date And Time

Hello everybody,

I have a problem with a date. I retrieve data from an Access database, where there are two fields, one for the date and one for the time. I have to join this two fields in my destination column, but I don't know how to do it...

I tried using a Derived column, in which I cast this two fields in string, concatenate them and then cast them back to datetime, but it doesn't work....

Does anyone knows how to resolve my problem?

Thanks

Did you get an error from SSIS when trying to parse the derived column code?

(DT_DATE)((DT_STR,4,1252)YEAR(<DATE_COLUMN>) + "-" + (DT_STR,2,1252)MONTH(<DATE_COLUMN>) + "-" + (DT_STR,2,1252)DAY(<DATE_COLUMN>) + " " + <TIME_COLUMN>)

|||

I get an error in using this string

(DT_DBDATE)(REPLACE((DT_STR,50,1252)Data,"/","-") + "T" + REPLACE((DT_STR,50,1252)Ora,".",""))

where "data" and "ora" are the fields with the date and the time I need

|||

can you try with this:
(DT_DATE)(REPLACE((DT_STR,50,1252)Data,"/","-") + " " + REPLACE((DT_STR,50,1252)Ora,".",":")). (I guess the datatype is datetime)

The replaces are only available if Data looks like "2006-03-13" and Ora looks like "20:15:10".

|||

Just as an aside...you might want to consider DT_DBTIMESTAMP for the reasons outlined here: http://blogs.conchango.com/jamiethomson/archive/2005/11/15/2399.aspx

As I said, this is an aside, it won't help you to parse the date out!

-Jamie

|||

I didn't know that :-) Thanks Jamie for the extra info.

So Teone, this is only a parsing problem. Your data type is not good to store date and time and the expression you're using isn't right either.
The 'T' for time (and not for two LOL) is really not needed.

Can you try with the expression I proposed in my second post?

|||it works except for seconds and minutes... they in fact are rounded to zero and to the next value up or down....|||it works except for seconds and minutes... they in fact are rounded to zero and to the next value up or down....|||

This is tuning then . You need to search for the right formatting of your input data and you'll find the way.