Convert Unix Time to oracle date
Moderators: chulett, rschirm, roy
Convert Unix Time to oracle date
Does anyone know how to convert a unix time ("1201546826")
into an oracle date in datastage.
I know it can be done in the sql but i am passing this into a procedure that i cannot change right now.
Thanks.
into an oracle date in datastage.
I know it can be done in the sql but i am passing this into a procedure that i cannot change right now.
Thanks.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No, you will need to adapt it. Your number is in seconds, the function expects an argument in days. (There are, in general, 86400 seconds in a day, so a simple division will suffice.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks that helped i almost the only issue i see is
where do this forumla
DateDaysSince1970ToTimeStamp(1233260928 /86400)
I am getting 2009-01-28
however when i run this query in oracle sql
select to_date('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(1233260928,'SECOND')from dual
I am getting 2009-01-29
where do this forumla
DateDaysSince1970ToTimeStamp(1233260928 /86400)
I am getting 2009-01-28
however when i run this query in oracle sql
select to_date('01-JAN-1970 00:00:00', 'DD-MON-YYYY HH24:MI:SS') + NUMTODSINTERVAL(1233260928,'SECOND')from dual
I am getting 2009-01-29
*************************************************************************
* Copyright (C) 2004, 1997-2003 Ascential Software Corporation. All Rights Reserved. *
* This code may be copied on condition that this copyright *
* notice is included as is in any code derived from this source. *
*************************************************************************
*
* Return current GMT / UTC time in YYYY-MM-DD HH:MM:SS.sss format (as used by
* other SDK routines and transforms).
*
*
* We use the SYSTEM(99) call to retrieve the number of seconds midnight
* since Jan 1st, 1970 GMT. and then compute the internal time format
* to the output conversion.
*
* Find the number of days since then.
InternalDate = DIV(Arg1,86400)
* Convert number of days to internal date format.
InternalDate += ICONV('01/01/1970','D4/DMY')
* Find seconds since midnight.
InternalTime = MOD(Arg1,86400)
* Now build the SDK timestamp string.
Ans = OCONV(InternalDate,'D4-YMD[4,2,2]'):' ':OCONV(InternalTime,'MTS'):'.000'
* Copyright (C) 2004, 1997-2003 Ascential Software Corporation. All Rights Reserved. *
* This code may be copied on condition that this copyright *
* notice is included as is in any code derived from this source. *
*************************************************************************
*
* Return current GMT / UTC time in YYYY-MM-DD HH:MM:SS.sss format (as used by
* other SDK routines and transforms).
*
*
* We use the SYSTEM(99) call to retrieve the number of seconds midnight
* since Jan 1st, 1970 GMT. and then compute the internal time format
* to the output conversion.
*
* Find the number of days since then.
InternalDate = DIV(Arg1,86400)
* Convert number of days to internal date format.
InternalDate += ICONV('01/01/1970','D4/DMY')
* Find seconds since midnight.
InternalTime = MOD(Arg1,86400)
* Now build the SDK timestamp string.
Ans = OCONV(InternalDate,'D4-YMD[4,2,2]'):' ':OCONV(InternalTime,'MTS'):'.000'