[EMAIL PROTECTED] wrote:
just a normal SQL alias name
Of course, I get it.
Haven't got it working though. Still the invalid use of null error.
Bart,
Here is what I get:
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> select
...> cast (
...> julianday(
...> substr(20061204, 1, 4) || '-' ||
...> substr(20061204, 5, 2) || '-' ||
...> substr(20061204, 7, 2)
...> ) - julianday('1900-01-01')
...> as integer
...> ) as excel_date
...> ;
39053
sqlite>
When I display the value of a cell with the formula =today() as an
integer it shows 39055. So there seems to be an off by 2 error (or, I
suspect, two off by one errors). One comes from the fact that excel
displays a value of zero as the invalid date 1900-01-00. So the minimum
legal value is 1, and therefore we need to add one to the difference
between the julianday numbers. This gives the following:
sqlite> select
...> cast (
...> julianday(
...> substr(20061204, 1, 4) || '-' ||
...> substr(20061204, 5, 2) || '-' ||
...> substr(20061204, 7, 2)
...> ) - julianday('1900-01-01') + 1
...> as integer
...> ) as excel_date
...> ;
39054
sqlite>
I can' t account for the other off by one error though. You could, of
course, just add 2 instead of 1 to get the right date from excel.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
