> It looks like Interbase uses a traditional date format based on an epoch.
It just does year * 10000 + month * 100 + day
I suppose I could something like this in SQL:
((lIBDate - ((lIBDate \ 10000) * 10000 + ((lIBDate - (lIBDate \ 10000) *
10000) \ 100) * 100)) + _
(153 * (((lIBDate - (lIBDate \ 10000) * 10000) \
100) + _
12 * ((14 - ((lIBDate - (lIBDate \ 10000)
* 10000) \ 100)) / 12) - 3) + 2) / 5 + _
((lIBDate \ 10000) + 4800 - ((14 -
((lIBDate - (lIBDate \ 10000) * 10000) \ 100)) / 12)) * _
365 + ((lIBDate \ 10000) + 4800 - _
((14 - ((lIBDate - (lIBDate \
10000) * 10000) \ 100)) / 12)) / 4 - 32083) - _
2415033
Except it looks a mess and it is one day out, I take it due to not declaring
variables as long as in my previously posted function.
RBS
-----Original Message-----
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: 03 December 2006 20:03
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd
It looks like Interbase uses a traditional date format based on an
epoch. That is a system where a base date is set (the epoch) and the
date is stored as an offset from that date. It is stored as an integer.
Sqlite uses a similar system, except that it uses a special epoch
compatible with other calendars and incorporates the time, storing all
of it in a 64 bit floating point format.
You should be able to translate the date into a yymmdd format of some
description from Interbase and use that to insert into Sqlite. The ISO
8601 date and time format is a widely used standard.
RB Smissaert wrote:
>>What do you mean by "integer format yyymmdd"?
>
>
> This is an Interbase database and the Create statement of such a field
would
> be like this:
>
> CREATE TABLE ENTRY(ADDED_DATE INTEGER, etc.
>
> Not sure what an epoch is, not sure how else to describe it.
>
> So, do you reckon I could, given the fact that I have an integer number in
> SQLite like 20061203, convert this in SQLite to another integer number
that
> would give the number of days since 31 December 1899? Or do you think that
I
> could make accurate dates comparisons in SQLite with 20061203 etc.? I
> suppose the trouble will be with weeks and days, years and months would be
> fine. So, for example it won't be that simple to say if 20061203 is more
> than 10 weeks past 20060920, whereas it will be easy to calculate that it
is
> less than 3 months passed that date.
> Is there such a thing as the VB/VBA DateSerial in SQLite? This means given
> the year, the month number and day number you can make a date?
>
> RBS
>
> -----Original Message-----
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: 03 December 2006 18:23
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd
>
> What do you mean by "integer format yyymmdd"? Is it an integer based on
> an epoch or is it a number like 20061203 stored in a 32 bit word?
>
> We use Sqlite format dates and times and thereby get good SQL capability
> and have a small library of date function which interface into Unix and
> Windows date and time retrieval APIs. A couple of simple Sqlite custom
> functions to do some date manipulation made the SQL simpler.
>
> Custom date functions are very easy to implement using Sqlite because
> you can use the Sqlite date routines and style as a basis.
>
> The Sqlite epoch based date format is elegant because it permits you to
> present date and time not only according to any time zone but to comply
> with more than just the Gregorian calendar - with Middle Eastern and
> Oriental ones should the need arise.
>
> RB Smissaert wrote:
>
>>Trouble is I need to import dates from Interbase that have the integer
>>format yyyymmdd. How would I convert that (at import) to the SQLite date
>>format? I do the import via an ADO recordset and then move the data via
>>SQLite inserts.
>>
>>RBS
>>
>>-----Original Message-----
>>From: John Stanton [mailto:[EMAIL PROTECTED]
>>Sent: 03 December 2006 17:01
>>To: sqlite-users@sqlite.org
>>Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd
>>
>>Why not use the internal Sqlite date format. Then date processing is
>>straight forward and you can use the conversion routines to present it
>>in the various national and ISO formats.
>>
>>Sqlite uses a very correct date storage format based on an epoch way
>>back in antiquity so that you can present it in any national format.
>>
>>Using the internal format date comparison is just a numeric compare,
>>which is efficient.
>>
>>If you need any special date presentation you can add a custome function
>>to Sqlite to achieve it from your SQL statement.
>>
>>RB Smissaert wrote:
>>
>>
>>>Just thought of one reason why it I need something else in SQLite than
>>>yyyymmdd in the date field and that is because I need date comparisons
>>>between different tables. So, I need to do: is fieldA + x days > fieldB?
>>>etc.
>>>This will be difficult with the yyyymmdd format. I could of course update
>>>all the date fields in a VBA loop, but that might be a bit slow. So, if
>>>anybody has an idea how to convert integer yyyymmdd to the Excel date
>>
>>format
>>
>>
>>>in SQLite I would be interested.
>>>
>>>RBS
>>>
>>>-----Original Message-----
>>>From: RB Smissaert [mailto:[EMAIL PROTECTED]
>>>Sent: 03 December 2006 15:37
>>>To: sqlite-users@sqlite.org
>>>Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd
>>>
>>>I think it is easier to do this in VBA and as the main work is done in a
>>
>>VBA
>>
>>
>>>array it is quite fast as well. Not as elegant maybe as doing it in
>>
>>SQLite,
>>
>>
>>>but it will do.
>>>
>>>RBS
>>>
>>>-----Original Message-----
>>>From: Fred Williams [mailto:[EMAIL PROTECTED]
>>>Sent: 03 December 2006 14:41
>>>To: sqlite-users@sqlite.org
>>>Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd
>>>
>>>Is there a reason you can use Excel's "Format Cells" to accomplish what
>>>you wish? Enter a "Custom" format of "yyyy\mm\dd" in a cell and enter
>>>"=today()" as a value in that cell. Have not fooled with Excel much
>>>lately, but I think you can even format a spreadsheet programmatically.
>>>
>>>Fred
>>>
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>From: RB Smissaert [mailto:[EMAIL PROTECTED]
>>>>Sent: Sunday, December 03, 2006 8:21 AM
>>>>To: sqlite-users@sqlite.org
>>>>Subject: [sqlite] Dealing with dates in the format yyyymmdd
>>>>
>>>>
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>>--------------------------------------------------------------
>>>>---------------
>>>>To unsubscribe, send email to [EMAIL PROTECTED]
>>>>--------------------------------------------------------------
>>>>---------------
>>>>
>>>
>>>
>>>
>>>
>
----------------------------------------------------------------------------
>
>>>-
>>>To unsubscribe, send email to [EMAIL PROTECTED]
>>>
>>
>>
>
----------------------------------------------------------------------------
>
>>>-
>>>
>>>
>>>
>>>
>>>
>>
>>
>
----------------------------------------------------------------------------
>
>>>-
>>>To unsubscribe, send email to [EMAIL PROTECTED]
>>>
>>
>>
>
----------------------------------------------------------------------------
>
>>>-
>>>
>>>
>>>
>>>
>>>
>>
>>
>
----------------------------------------------------------------------------
>
>>-
>>
>>
>>>To unsubscribe, send email to [EMAIL PROTECTED]
>>>
>>
>>
>
----------------------------------------------------------------------------
>
>>-
>>
>>
>>
>>
>
>
----------------------------------------------------------------------------
>
>>-
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>
>
>
----------------------------------------------------------------------------
>
>>-
>>
>>
>>
>>
>>
>
>
----------------------------------------------------------------------------
> -
>
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>
>
>
----------------------------------------------------------------------------
> -
>
>
>
>
----------------------------------------------------------------------------
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
----------------------------------------------------------------------------
> -
>
>
>
>
>
----------------------------------------------------------------------------
-
> To unsubscribe, send email to [EMAIL PROTECTED]
>
----------------------------------------------------------------------------
-
>
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
