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] -----------------------------------------------------------------------------
