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