[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]
-----------------------------------------------------------------------------
