OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » date format

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date format [message #7101]

Wed, 21 May 2003 02:49

Dani
Messages: 15
Registered: October 2000

Junior Member

hi everybody,
I have a table with many fields but I need only two
TAB odm_X(dateStart number(20),dateFinish number(20))

data in the fields (1053381600000,1054332000000
1051221600000,1054332000000....)
I know that number are to indicate a DATE but I don't know which one.
I need to transform that number in a Oracle date format like "DD-MM-YYYY"
from SQL*plus I done:
select to_date(dateStart,'dd-mm-yyyy') from odm_X;
but I got an error ORA-01861.

please,help me if you can.
thanx Dani

Report message to a moderator

Re: date format [message #7102 is a reply to message #7101]

Wed, 21 May 2003 05:36

Maaher
Messages: 7065
Registered: December 2001

Senior Member

We cannot be 100% sure but here's my 2cents.
I suspect it to be a Java date format. If I recall correctly it is the number of millisecs past since 1/1/1970 00H 00'00'' GMT. So to transfer these dates in SQL, you need to do the following.

1. Transfer the msecs (original value) to days for Oracle calculation:
1053381600000/1000 --> msecs to seconds
==> 1053381600
1053381600/60 --> secondss to minutes
==> 17556360
17556360/60 --> minutes to hours
==>292606
292606/24 --> hours to days
==> 12191,9166666666666666667...
In short: Original_value/1000/60/60/24
= Original_value/86400000
2. Add this number to 1 jan 1970 midnight.

This gives the following result:

SQL> Select to_char(to_date('01/01/1970','DD/MM/YYYY')
  2                          + (orig_val/86400000),'DD/MM/YYYY HH24:MI') your_date
  3       , orig_val original_value
  4      From test
  5  /

YOUR_DATE                   ORIGINAL_VALUE
---------------- -------------------------
19/05/2003 22:00             1053381600000
30/05/2003 22:00             1054332000000
24/04/2003 22:00             1051221600000
30/05/2003 22:00             1054332000000

HTH,
MHE

Report message to a moderator

Re: date format [message #7106 is a reply to message #7102]

Wed, 21 May 2003 07:12

Dani
Messages: 15
Registered: October 2000

Junior Member

Hi Maaher,
you weren't wrong is a Java date format,I did ask to a colleague that knows Java ('cause I don't know).
anyway the example you send me works well for my purpose.
many thanks Dani

Report message to a moderator

Re: date format [message #7123 is a reply to message #7101]

Thu, 22 May 2003 03:40

Deepak
Messages: 111
Registered: December 1999

Senior Member

Hi,

Please give the detail of number ?
How the date is store in Number Format then only you can get some answer ,how to convert date into number...

Please first convert number in to to char then again into to_date.

to_date (to_char(dateStart,'dd-mm-yyyy'),'dd-mm-yyyy')

Report message to a moderator

Re: date format [message #7125 is a reply to message #7123]

Thu, 22 May 2003 05:21

Dani
Messages: 15
Registered: October 2000

Junior Member

Hi,
There is an outer Java application that makes the insert into the DB. Unfortunately I don't know Java and I cannot get the JBean that does the convertion's date.
The only thing I can see using select is lots of number representing dates.
A friend told me that it is the number of millisecs past since 1/1/1970
so that's what I worked out with Maaher :
SQL> select to_char(to_date('01-01-1970','dd-mm-yyyy')
+ round(1053381600000 / 86400000)
,'dd-mm-yyyy') DATE
from dual;

DATE
----------
20-05-2003

Hope what I said it is enough to let you understand
my problem..

Thank you

Report message to a moderator

Previous Topic:	Table Vs View
Next Topic:	group functions tricky question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:10:15 CDT 2024