Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

formatting with To_Date

285992Apr 12 2005 — edited Apr 12 2005
I was doing some testing with Julian dates and I got some strange results from To_Date. It looks like the formatting in To_Date is dropping zeros. I am using Oracle 9i. I did a search on the site but couldn't find anything about this issue. Has anyone else seen this issue before? If this is a bug, who should I submit it to? thanks!

-- get the Julian date for 1/13/99
SELECT To_Date(2451192, 'J') FROM dual;

-- test displaying seconds past midnight appended to Julian date
-- get time one second before 5 pm
-- displays 4:59:59
SELECT To_Date(2451192.61199, 'J.SSSSS') FROM dual;

-- get time at 5:00 pm, hey, what happened to the time???
-- both queries return 12:10:12 am for time
SELECT To_Date(2451192.61200, 'J.SSSSS') FROM dual;
SELECT To_Date(2451192.612, 'J.SSSSS') FROM dual;

-- now get one second after 5 pm,
-- displays 5:00:01 pm
SELECT To_Date(2451192.61201, 'J.SSSSS') FROM dual;

-- but then the following works as expected
-- displays 5:00:00 pm
SELECT To_Date(61200, 'SSSSS') FROM dual;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 10 2005
Added on Apr 12 2005
1 comment
526 views