| Date/Time Format [message #85688] |
Wed, 21 July 2004 07:08  |
Mahe
Messages: 2
Registered: July 2004
|
Junior Member |
|
|
Hello All,
I am working on a date field which is a 'double' (Eg: 37573.50562). I need to convert this number into date/time format. I even know how it works but I am kinda stuck with it when the time part comes.
For the above example the value for date/time is gonna be "11/13/2002 12:08:06 PM". You can paste the number in excel and when you change the column format to Date you can see this value. Actually Excel uses "Datevalue and Time value" functions, but I dont see any such functions at the database level.
Can anybody please help me with this?
Thank you,
Regards,
Mahe..
|
|
|
|
| Re: Date/Time Format [message #85689 is a reply to message #85688] |
Wed, 21 July 2004 08:42  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Apparently they start counting at:
SQL> select to_date('13-11-2002', 'dd-mm-yyyy') - 37573 from dual;
TO_DATE('1
----------
30-12-1899
so that's our starting point.
SQL> select to_char(to_date('30-12-1899', 'dd-mm-yyyy') + 37573.50562, 'dd-mm-yyyy hh24:mi:ss')
2 from dual;
TO_CHAR(TO_DATE('30
-------------------
13-11-2002 12:08:06
hth
|
|
|
|
| Re: Date/Time Format [message #85691 is a reply to message #85689] |
Wed, 21 July 2004 09:12 |
Mahe
Messages: 2
Registered: July 2004
|
Junior Member |
|
|
Hi Frank,
Thanks for your reply. Actaully the starting point is Jan 01 1900.What I mean to say is
Jan 01 1900 -----> 1
Jan 02 1900 -----> 2 and so on...
so when I say 37573, that means it is 37573 days ahead starting from Jan 01 1900...
Can you suggest me a method by which I can calcualte keeping in acoount the year (Leap year)
Thanks,
Mahe
|
|
|
|
| Re: Date/Time Format [message #85694 is a reply to message #85691] |
Wed, 21 July 2004 12:26 |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
No, the starting point is Dec 31, 1899.
Dec 31, 1899 ---> 0
Jan 01, 1900 ---> 1 and so on
so just to get 1 to equal jan 01, 1900, do as you were already told.
and you don't need to handle leap years - oracle does that for you. If you mean the fact that excel screwed up and makes 1900 a leap year, when it's not, well, we don't try to fix excel around here.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
