RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

I am dealing with a clinical (patients) database and there are only 8
patients with a date of birth before 1900-02-28, so not a major problem.
Clinical data is all well after that. I will see what the speed penalty is
from doing the when then else and if it is small then apply it to the date
of birth only. Thanks again for getting me on the right track.

RBS

-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 04 December 2006 18:44
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd

[EMAIL PROTECTED] wrote:
> Great stuff.
> For your information it needs to  be:
>  julianday('1899-12-30')
>
>   
Yes, that will accomplish the same thing.

There are really two issues, the fact that excel uses 1 (not 0) as the 
serial number for 1900-01-01, and the fact that it incorrectly assumes 
that 1900 was a leap year and therefore that there was a day 1900-02-29 
(the excel date bug).

The real base date is 1900-01-01, but you need to add one to the 
difference between the julian day numbers to correct for the base serial 
number offset, and you need to add another one for any date after 
1900-02-28 to correct for the leap year bug.

select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1900-01-01') + 1
else julianday('now') - julianday('1900-01-01') + 2
end as excel_date;

You get the same effect if you change the base date from 1900-01-01 to 
1899-12-31 or 1899-12-30 (i.e this adds 1 or 2 days).

select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1899-12-31')
else julianday('now') - julianday('1899-12-30')
end as excel_date;

If all your dates are after 1900-02-28 then you can safely skip the test 
and use only the later base date.

select julianday('now') - julianday('1899-12-30') as excel_date;

Dennis Cote

----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------