My source table has a Unix timestamp stored in a INT field. I want to convert this using Data services designer to a Datetime format yyyy-mm-dd hh24:mi:ss.
Using the convertion below will end up with errors.
to_date(seam_event.detectiontime, 'yyyy-mm-dd hh24:mi:ss')9832 6860 RUN-053001 8-11-2012 17:10:16 Invalid value <Month: 88> for date <1347882765>. Context: Column <Join Project Column detectiontime>.
Any ideas?
bas_vdl 
(BOB member since 2012-11-08)
Can you try using this
to_date(to_char(seam_event.detectiontime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')Tarunvig 
(BOB member since 2011-01-18)
You cannot directly convert an INT data type to a DATE/DATETIME data type. It doesn’t understand what the value in the INT column represents. Is the number the number of seconds past midnight? Number of days since the dawn of time? Get it?
You have to first convert the INT to a VARCHAR. This assumes that your INT column is in a format that lends itself to producing something meaningful as a VARCHAR. For example, I have a system that stores date values as 20120101 in a NUMBER(8) data type. I know the format the number is store as so converting it directly to VARCHAR makes complete sense. In this case the conversion functions would be:
to_date(to_char(sk_dpdate), 'YYYYMMDD')This works in Oracle just fine. But I don’t believe that DS will accept that so you have to do this:
to_date(cast(sk_dpdate, 'varchar(8)'), 'YYYYMMDD')eganjp (BOB member since 2007-09-12)
This is not working either.
bas_vdl 
(BOB member since 2012-11-08)
[quote=“bas_vdl”]
Of course it won’t. See my post above.
eganjp 
(BOB member since 2007-09-12)
[quote=“eganjp”]
Yes it won’t.
As Jim says, do explicit conversion. That code will exactly work in 11.7 !
But starting 12.0, DS have enhanced or should say the coding standard on DATE conversions became stringent enough that this glitch won’t work!
ganeshxp (BOB member since 2008-07-17)