Thanks, will try that.
What does the: % 100 do and what the Excel_date ?
RBS
> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format yyyymmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/yyyy (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/yyyy if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/yyyy so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 10000 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [EMAIL PROTECTED]
> -----------------------------------------------------------------------------
>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
