No, these particular fields don't use the IB date type, but a simple integer. I have managed to get the Interbase dll ib_udf.dll working now and that has a substr function that seems to do the job. So now I can make 03-12-2006 type of dates and hopefully SQLite will accept that as a date. In a way it would still be better though to make Excel integer dates as that will save some trouble when dumping data in the sheets. Maybe I could make an Interbase UDF for that. Or maybe the SQLite dates are fine as well when dumped in an Excel sheet.
RBS -----Original Message----- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 04 December 2006 00:41 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd I suspect that you are not using the Interbase date type, which is a 64 bit object encapsulating date and time and having an epoch November 17, 1898. You seem to have some private packed format. Interbase has a rich set of date and time handling functions built in, provided you use the date type. You have two options in your application. The first is to just do with Sqlite what you did with interbase and have your own private date format. The second is to transform your dates into the Sqlite date format. It depends upon your application and reuse of legacy code. RB Smissaert wrote: > 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] > ---------------------------------------------------------------------------- - > ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
