Convert UTC to date 1

[scottyjohn]

Hi all,
I got some help before about how to display results of an oracle timestamp field in an SQL query as format dd/mm/yyyy or indeed as HH24:MI:SS. I now need to do the same but from a field which is UTC and has column type INTEGER. Can anyone give me the correct syntax for this as Im struggling to find any posts or web info on this.

John

 

[SantaMufasa]

Scotty,

Here is some raw SQL code:

select to_char(to_date('01.01.70','dd.mm.rr')+
    (1157620440/(60*60*24)),'dd-mm-yyyy hh24:mi:ss')now
  from dual;

NOW
-------------------
07-09-2006 09:14:00

1 row selected.

You can, however, tighten things up a bit by placing the UTC conversions into user-defined functions.

UTC-to-Oracle-date-format function:

create or replace function utc_to_oracle (utc_in in number) return date is
begin
 return to_date('01.01.70','dd.mm.rr')+(utc_in/(60*60*24));
end;
/

Function created.

select to_char(utc_to_oracle(1157620440),'yyyy-mm-dd hh24:mi:ss')now_from_utc from dual;

NOW_FROM_UTC
-------------------
2006-09-07 09:14:00

1 row selected.

UTC-from-Oracle-date-format function:

create or replace function utc_from_oracle (date_in in date) return number is
begin
 return (date_in-to_date('01.01.70','dd.mm.rr'))*(60*60*24);
end;
/

Function created.

select utc_from_oracle(sysdate)now_in_utc from dual;

NOW_IN_UTC
----------
1157620440

1 row selected.

Let us know if this is useful stuff.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[scottyjohn]

Thank you Santa,
Xmas has arrived early! The raw SQL was exactly what I need and works perfectly. have a good weekend!

John