Re: How to convert this date to a meaningful date format

From: Harald Maier <maierh_at_myself.com>
Date: Thu, 21 Aug 2003 12:05:24 +0200
Message-ID: <m3lltnxr4r.fsf@ate.maierh>

"Joe Smith" <nospam_at_nospam.com> writes:

>>
>> I have a date column whose date formate is like '1061053668503'. How do
>> I convert this date format into 'yyyy-mm-dd hh24:mi:ss' ? THANKS!
>
> It seems a timestamp in ms (number of ms from 1 January 1970).
>
> Converting this number in java, it returns:
> Sat Aug 16 19:07:48 CEST 2003
>
> I'm not a Oracle expert... just pointing this out so someone else
> can give the answer.

Here some ideas. I am too not an expert with date functions. Final formating is to you.

Harald

select

to_timestamp_tz(
to_char(
to_date('01.01.1970', 'dd.mm.yyyy') +

1061053668503/(1000*24*60*60),
'dd.mm.yyyy hh24:mi:ss'),
'dd.mm.yyyy hh24:mi:ss')
from dual;

TO_TIMESTAMP_TZ(TO_CHAR(TO_DATE('01.01.1970','DD.MM.YYYY')+1061053668503/(1

---

16.08.03 17:07:49,000000000 +02:00 Received on Thu Aug 21 2003 - 05:05:24 CDT