It has been a long time since I worked with Interbase, but I don't remember the format you describe as being an Interbase date format. Is this some kind of cobbled up date field? I seem to remember that Interbase supported pretty much standard date management routines, including date formatting for various uses.
I don't have Interbase installed anywhere right now or I would look into this issue a little closer. I do know that I did both data imports and exports to and from Interbase with little or no problems other than the normal self inflicted wounds. Fred > -----Original Message----- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 03, 2006 3:40 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd > > > If I try to convert the integer Interbase date with standard SQL I get > something like this: > > CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 10000 AS INT) > * 10000)) - " & > _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 10000 AS INT) * > 10000) AS INT) > AS INT) || '/' || " & "CAST((E.ADDED_DATE - > CAST(E.ADDED_DATE / 10000 AS > INT) * 10000) / 100 AS INT) || '/' || " & "CAST(E.ADDED_DATE > / 10000 AS > INT) AS VARCHAR(10)) > > But I haven't got it working yet. > > RBS > > -----Original Message----- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: 03 December 2006 21:05 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd > > Is the date function as in: > > Function Overview > > Five date and time functions are available, as follows: > > 1. date( timestring, modifier, modifier, ...) ? > > Will give that a go. > > RBS > > > -----Original Message----- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: 03 December 2006 20:46 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd > > Why not use Interbase to transform the date into a character string, > like an ISI8601 date or yymmdd, dd-mm-yyyy etc and then use > the Sqlite > date function to insert it into Sqlite? > > RB Smissaert wrote: > >>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] > > > -------------------------------------------------------------- > -------------- > - > > > > > -------------------------------------------------------------- > -------------- > - > 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] -----------------------------------------------------------------------------
