Some systems work with a different data type called Unix (IBM2) timestamp. These are usually integer numbers and you might have to have a need to convert them to Oracle date format or vice verse. The script below will help you out.
OFFSETS:
- GMT = 0
- GMT+1 = 3600
- GMT+2 = 7200
- GMT+3 = 10800
- GMT+4 = 14400
- GMT+5 = 18000
- GMT+6 = 21600
- GMT+7 = 25200
- GMT+8 = 28800
- GMT+9 = 32400
- GMT+10 = 36000
- GMT+11 = 39600
- GMT+12 = 43200
--Convert date (:MyDate) to UNIX format --UNIX_FORMAT = ( :MyDate - DATE '1970-01-01' ) * 86400 - OFFSET ; SELECT ( sysdate - DATE '1970-01-01' ) * 86400 - OFFSET as unix_format FROM dual; --Convert UNIX timestamp (:UnixTstmp) format to date --MY_DATE = DATE '1970-01-01' + ( :UnixTstmp + OFFSET ) /86400 ; SELECT ( DATE '1970-01-01' + ( UnixTimeStamp + OFFSET ) / 86400 as oracle_date_format FROM dual;