Don't know that much about it myself, but there are no functions for this that I know of. There are third party UDF's though and that is probably the best way forward. We are still on Interbase 5.6 and I think the latest is 7.5, so that might explain something.
RBS -----Original Message----- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 23:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd I know nothing of Interbase, but cannot imagine that it does not have a set of date manipulation functions. RB Smissaert wrote: > 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] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
