Thanks , will try that.
What is as excel_date?
Is this a variable or is this jus plain SQL against SQLite?
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.
>>
>> RBS
>>
>>
> This should give you the excel integer date format directll.
>
> select
> cast (
> julianday(
> substr(datefield, 1, 4) || '-' ||
> substr(datefield, 5, 2) || '-' ||
> substr(datefield, 7,2)
> ) - 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]
-----------------------------------------------------------------------------
