Dennis,
Excel dates start from 30 December 1899.
Then there is of course the famous Excel date bug.
Just type in Google: Excel date bug
and you will see what I am talking about.
RBS
> [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]
> -----------------------------------------------------------------------------
>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
