Re: Date problem ---- SOS

From: Steve Butler <sbut-is_at_seatimes.com>
Date: 1995/11/10
Message-ID: <Pine.SUN.3.91.951110102808.8403D-100000_at_seatimes>#1/1

On 9 Nov 1995, Dinar Dhond wrote:
> We have a legacy application which store date in a form similar to
> Julian date with 1 starting at 1-1-1969.
> e.g. 10175 = 09-Nov-1995

I think there is a problem here. Either you have more years in your calendar than I do in mine or there is a typo! Personally, I think it is a typo. From 1/1/69 to 1/1/95 there is 26 years. Only 6 of these are leap years. Now, 09-Nov is day # 313 for 1995. So:  

            20*365  = 7201
             6*366  = 2196
                    +  313
                      ----
                      9809  which is 366 days shy of 110175

Wonder of wonders, there is exactly 366 days from 09-Nov-95 to 09-Nov-96.

Therefore, I think 10175 ===> 09-Nov-96. [Or was day one on 1-Jan-68, since '68 also had 366 days in it?]

> I would like to be able to convert the number viz. 10175 in the form
> mm-dd-yy. Does anybody know an algorithm that I can use to convert the
> number to a date.

I presume you are using ORACLE. The following (using 01-Jan-68 as the starting point) will give you the idea:

>select to_date('12/31/67','mm/dd/yy')+10175 from dual;
TO_DATE('

---

09-NOV-95
1 row selected.

Does this help or did you need a non-Oracle solution?

NOTE: If bringing it back from a column on a table you could do the following:

    Select to_date('12/31/67','mm/dd/yy')+mycolumn from mytable;

+----------------------------------------------------+
| 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 Fri Nov 10 1995 - 00:00:00 CET