Well, these fields I am talking about hold date information but they have nil to do with dates in that you can't do any date manipulation on the data. There also are proper date-time fields in the database and they hold something entirely different. The first field (with yyyymmdd) hold a long data type and the second one holds a date data type.
RBS > The Interbase date type is a simple integer! > > RB Smissaert wrote: >> 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] >> ----------------------------------------------------------------------------- >> > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
