Re: Date problem ---- SOS
Date: 1995/11/15
Message-ID: <Pine.SUN.3.91.951115172428.5475D-100000_at_seatimes>#1/1
On Fri, 10 Nov 1995, Louise Miller wrote:
> For those of us who are "mathmatically challenged,"
> would someone please explain this?
Ounch! I'm afraid I may start responding at the wrong level and make you really mad at me for "belittling" you.
So, let me take the low road and you can believe that I'm responding to someone else if I aim too low!!
The original requestor had a column that contained the number of days since 12/31/67. That is, day 1 was 1/1/68. He was wanting a quick way to convert that number into the actual date. ORACLE keeps it's date in an internal format where the whole number portion is the number of days since some date in the far distant past (it doesn't really match up with the true astronomical date due to a bug they have believing there was a year 0 between 1 BC and 1 AD).
Therefore, if we can add the number of days in the requestors column to some other number, we should be able to coax ORACLE into doing the date conversion for us. We see that ORACLE stores the date 31 DEC 67 as that correct number (2439856). Adding that number to the number of days since then gives us the number that ORACLE would have internally for the date.
Try the following:
>select to_char(to_date('12/31/67','mm/dd/yy'),'J') from dual
TO_CHAR(TO_DATE('12/31/67','MM/DD/YY'),'J')
2439856 1 row selected.
>select to_date(2439856+1,'J') from dual
TO_DATE(2
01-JAN-68
1 row selected.
>select to_date('12/31/67','mm/dd/yy')+1 from dual
TO_DATE('
01-JAN-68
1 row selected.
Have fun!
+----------------------------------------------------+ | Steve Butler Voice: 206-464-2998 | | The Seattle Times Fax: 206-382-8898 | | PO Box 70 Internet: sbut-is_at_seatimes.com | | Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA | +----------------------------------------------------+All standard and non-standard disclaimers apply. All other sources are annonymous. Received on Wed Nov 15 1995 - 00:00:00 CET