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