Unix date format to YYYY-MM-DD HH24:MM:SS

dataservices job design
1

:wave:

I’m having a hard time converting a date from a SQL Server 2005 DB to an Oracle DB

The SQL DB has a field with the the date in the Unix format ( No. of seconds since 01-01-1970), and I need to import it to ORACLE in the format YYYY-MM-DD HH24:MM:SS.

I’ve tryed to use the SQL Server command as dateadd(ss,OPEN_DATE,‘01-01-1970’) inside a SQL transformation, but DI is eneable to intepreter it and just displayes an error message and is enable to save the query. (BUG ? )

I’ve also tryed to convert the numb of secons into julian, but the DI command julian_to_date only takes the integer part and so it only returns the date but not the hours, minutes and seconds
julian_to_date(julian(to_date(‘1970-01-01’,‘YYYY-MM-DD’)) + OPEN_DATE/(606024))

Anyone has sugestions how to get 1216201879 transformed in 2008-07-16 09:51:19.000 ?

My brain is running out of ideas.
Inputs to convert it inside the DI are very welcomed.

Ps. I’m using DI 11.5.3.4 against a Oracle 9i DB and the data source is a SQL 2005.

Thanks

nunovsky (BOB member since 2006-09-11)

2

What datatype are the source and target columns you read/load the date?

Werner Daehn :de: (BOB member since 2004-12-17)

3

The source is int (SQL) and the the target is date (oracle)

nunovsky (BOB member since 2006-09-11)

4

to_date(‘1970.01.01’,‘YYYY.MM.DD’) + 1216201879/60/60/24

should do the trick. Your idea with the julian was right, except its datatype is date, not datetime. Guess that’s why you lost the time portion.

Werner Daehn :de: (BOB member since 2004-12-17)