RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

> You might want to check the records for the patients with DOB
> before 1900-01-01

Already taken care of that long time ago.
As said, I am more familiar with Excel than with SQLite, but catching up.

RBS

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 04 December 2006 21:23
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd

RB Smissaert wrote:
> I am dealing with a clinical (patients) database and there are only 8
> patients with a date of birth before 1900-02-28, so not a major problem.
> Clinical data is all well after that. I will see what the speed penalty is
> from doing the when then else and if it is small then apply it to the date
> of birth only. Thanks again for getting me on the right track.
>
>   
Bart,

You might want to check the records for the patients with DOB before 
1900-01-01, since these dates can't be expressed as excel date serial 
numbers (the lowest valid value is 1 or 1900-01-01). Note, that julian 
day numbers can express dates back to 4700 BC, but Excel can't.

The testing needed to fix the dates for excel's bug only applies to 
dates in the first two month of the year 1900, so it can probably be 
safely skipped.

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

RB Smissaert wrote:

I am dealing with a clinical (patients) database and there are only 8
patients with a date of birth before 1900-02-28, so not a major problem.
Clinical data is all well after that. I will see what the speed penalty is
from doing the when then else and if it is small then apply it to the date
of birth only. Thanks again for getting me on the right track.

  

Bart,

You might want to check the records for the patients with DOB before 
1900-01-01, since these dates can't be expressed as excel date serial 
numbers (the lowest valid value is 1 or 1900-01-01). Note, that julian 
day numbers can express dates back to 4700 BC, but Excel can't.


The testing needed to fix the dates for excel's bug only applies to 
dates in the first two month of the year 1900, so it can probably be 
safely skipped.


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

It is a very common algorithm.  From memory it is implemenented in 
Sqlite, for example.


[EMAIL PROTECTED] wrote:

can convert to seconds since start of epoch, do the arithmetic,
then convert back



Don't get you. It would be quite a calculation, with leap years etc.

RBS



On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote:


Still have the problem though how to compare dates in SQLite when the
format
is the integer mmdd. Maybe I will need some custom SQLite function.


What's difficult about comparing integers of the form mmdd?

Comparing them is easy: normal integer comparison operators work just
fine.

Date arithmetic with mmdd integers, OTOH, is more difficult; but you
can convert to seconds since start of epoch, do the arithmetic, then
convert back.

Nico
--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-









-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

They are in the same format as Interbase dates.  Could there be a schema 
error and they have been wrongly identified?


Can you transfer them to be ing correct date types in Interbase, then 
you just use very conventional tools for your transfer?


[EMAIL PROTECTED] wrote:

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 mmdd) 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 mmdd

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 mmdd

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 / 1 AS INT) * 1))
- "


&


_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS
INT)
AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1
AS
INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1
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 mmdd

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 mmdd

Why not use Interbase to transform the date into a character string,
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1)
*
1) \ 100) * 100)) + _
  (153 * (((lIBDate - (lIBDate \ 1) * 1)
\
100) + _
  12 * ((14 - ((lIBDate - (lIBDate \


1)





* 1) \ 100)) / 12) - 3) + 2) / 5 + _
  ((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
  365 + ((lIBDate \ 1) + 4800 - _
 ((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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: sq

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

I am dealing with a clinical (patients) database and there are only 8
patients with a date of birth before 1900-02-28, so not a major problem.
Clinical data is all well after that. I will see what the speed penalty is
from doing the when then else and if it is small then apply it to the date
of birth only. Thanks again for getting me on the right track.

RBS

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: 04 December 2006 18:44
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format mmdd

[EMAIL PROTECTED] wrote:
> Great stuff.
> For your information it needs to  be:
>  julianday('1899-12-30')
>
>   
Yes, that will accomplish the same thing.

There are really two issues, the fact that excel uses 1 (not 0) as the 
serial number for 1900-01-01, and the fact that it incorrectly assumes 
that 1900 was a leap year and therefore that there was a day 1900-02-29 
(the excel date bug).

The real base date is 1900-01-01, but you need to add one to the 
difference between the julian day numbers to correct for the base serial 
number offset, and you need to add another one for any date after 
1900-02-28 to correct for the leap year bug.

select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1900-01-01') + 1
else julianday('now') - julianday('1900-01-01') + 2
end as excel_date;

You get the same effect if you change the base date from 1900-01-01 to 
1899-12-31 or 1899-12-30 (i.e this adds 1 or 2 days).

select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1899-12-31')
else julianday('now') - julianday('1899-12-30')
end as excel_date;

If all your dates are after 1900-02-28 then you can safely skip the test 
and use only the later base date.

select julianday('now') - julianday('1899-12-30') as excel_date;

Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Great stuff.
For your information it needs to  be:
 julianday('1899-12-30')

  

Yes, that will accomplish the same thing.

There are really two issues, the fact that excel uses 1 (not 0) as the 
serial number for 1900-01-01, and the fact that it incorrectly assumes 
that 1900 was a leap year and therefore that there was a day 1900-02-29 
(the excel date bug).


The real base date is 1900-01-01, but you need to add one to the 
difference between the julian day numbers to correct for the base serial 
number offset, and you need to add another one for any date after 
1900-02-28 to correct for the leap year bug.


select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1900-01-01') + 1
else julianday('now') - julianday('1900-01-01') + 2
end as excel_date;

You get the same effect if you change the base date from 1900-01-01 to 
1899-12-31 or 1899-12-30 (i.e this adds 1 or 2 days).


select case
when julianday('now') <= julianday('1900-02-28')
then julianday('now') - julianday('1899-12-31')
else julianday('now') - julianday('1899-12-30')
end as excel_date;

If all your dates are after 1900-02-28 then you can safely skip the test 
and use only the later base date.


select julianday('now') - julianday('1899-12-30') as excel_date;

Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Dennis,

Excel dates start from 30 December 1899.
Then there is of course the famous Excel date bug.
Just type in Google: Excel date bug
and you will see what I am talking about.

RBS

> [EMAIL PROTECTED] wrote:
>>> just a normal SQL alias name
>>>
>>
>> Of course, I get it.
>> Haven't got it working though. Still the invalid use of null error.
>>
>>
> Bart,
>
> Here is what I get:
>
> SQLite version 3.3.5
> Enter ".help" for instructions
> sqlite> select
>...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01')
>...> as integer
>...> ) as excel_date
>...> ;
> 39053
> sqlite>
>
> When I display the value of a cell with the formula =today() as an
> integer it shows 39055. So there seems to be an off by 2 error (or, I
> suspect, two off by one errors). One comes from the fact that excel
> displays a value of zero as the invalid date 1900-01-00. So the minimum
> legal value is 1, and therefore we need to add one to the difference
> between the julianday numbers. This gives the following:
>
> sqlite> select
>...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01') + 1
>...> as integer
>...> ) as excel_date
>...> ;
> 39054
> sqlite>
>
> I can' t account for the other off by one error though. You could, of
> course, just add 2 instead of 1 to get the right date from excel.
>
> HTH
> Dennis Cote
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Great stuff.
For your information it needs to  be:
 julianday('1899-12-30')

RBS


> [EMAIL PROTECTED] wrote:
>> Tried it, but get the error invalid use of null.
>> The field I tried it on had no NULL values.
>> Will figure it out.
>>
>>
> Bart,
>
> I should have tested what I posted first. There is a small problem, the
> julianday function needs a date string in -MM-DD format, but the
> numeric calculations don't insert leading zeros for months and days less
> than 10. Here i sa modified version that does leading zero insertion.
> Even more reason to use the original substring based version.
>
> select
> cast (
> julianday(
> cast(20061204 / 1 as integer) || '-' ||
> substr('00' || (cast(20061204 / 100 as integer) % 100), -2,
> 2) || '-' ||
> substr('00' || (20061204 % 100), -2, 2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> ;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Hi Dennis,

Got this working now, great stuff and thanks!
Will do some testing to see what is faster and report back here.

RBS

> [EMAIL PROTECTED] wrote:
>> Thanks , will try that.
>> What is as excel_date?
>> Is this a variable or is this jus plain SQL against SQLite?
>>
>>
> Bart,
>
> excel_date is just a normal SQL alias name for the complicated
> expression. The string 'execl_date' will be returned as the name of this
> result column. This name is only used if you look at or display the
> column name, like you would if you use the sqlite shell to execute the
> command.
>
> P.S. see my subsequent post after I noticed that your datefield was an
> integer raher than a string. Note that this string based version will
> also work because sqlite automatically converts the integer datefield
> value into a string to pass it to the substr function.
>
> HTH
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Tried it, but get the error invalid use of null.
The field I tried it on had no NULL values.
Will figure it out.

  

Bart,

I should have tested what I posted first. There is a small problem, the 
julianday function needs a date string in -MM-DD format, but the 
numeric calculations don't insert leading zeros for months and days less 
than 10. Here i sa modified version that does leading zero insertion. 
Even more reason to use the original substring based version.


select
   cast (
   julianday(
   cast(20061204 / 1 as integer) || '-' ||
   substr('00' || (cast(20061204 / 100 as integer) % 100), -2, 
2) || '-' ||

   substr('00' || (20061204 % 100), -2, 2)
   ) - julianday('1900-01-01')
   as integer
   ) as excel_date
;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

> just a normal SQL alias name

Of course, I get it.
Haven't got it working though. Still the invalid use of null error.

RBS


> [EMAIL PROTECTED] wrote:
>> Thanks , will try that.
>> What is as excel_date?
>> Is this a variable or is this jus plain SQL against SQLite?
>>
>>
> Bart,
>
> excel_date is just a normal SQL alias name for the complicated
> expression. The string 'execl_date' will be returned as the name of this
> result column. This name is only used if you look at or display the
> column name, like you would if you use the sqlite shell to execute the
> command.
>
> P.S. see my subsequent post after I noticed that your datefield was an
> integer raher than a string. Note that this string based version will
> also work because sqlite automatically converts the integer datefield
> value into a string to pass it to the substr function.
>
> HTH
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

> can convert to seconds since start of epoch, do the arithmetic,
> then convert back

Don't get you. It would be quite a calculation, with leap years etc.

RBS

> On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote:
>> Still have the problem though how to compare dates in SQLite when the
>> format
>> is the integer mmdd. Maybe I will need some custom SQLite function.
>
> What's difficult about comparing integers of the form mmdd?
>
> Comparing them is easy: normal integer comparison operators work just
> fine.
>
> Date arithmetic with mmdd integers, OTOH, is more difficult; but you
> can convert to seconds since start of epoch, do the arithmetic, then
> convert back.
>
> Nico
> --
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Tried it, but get the error invalid use of null.
The field I tried it on had no NULL values.
Will figure it out.

RBS

> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (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/ 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/  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.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Nicolas Williams]

On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote:
> Still have the problem though how to compare dates in SQLite when the format
> is the integer mmdd. Maybe I will need some custom SQLite function.

What's difficult about comparing integers of the form mmdd?

Comparing them is easy: normal integer comparison operators work just
fine.

Date arithmetic with mmdd integers, OTOH, is more difficult; but you
can convert to seconds since start of epoch, do the arithmetic, then
convert back.

Nico
-- 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Thanks, will try that.
What does the: % 100 do and what the Excel_date  ?

  
The % is the modulus operator. It returns the remainder after division 
by a number. For example, 1234 divided by 100 gives 12 and a remainder 
of 34. The '/' division operator returns the quotient 12, and the % 
modulus operator returns the remainder 34.


   1234 / 100 => 12
   1234 % 100 => 34

In hind sight I think the substr based version is clearer, bu this 
version may provide higher performance due to ferwer format conversions. 
You can try both and see if there is any measurable speed difference.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

[EMAIL PROTECTED] wrote:

Thanks , will try that.
What is as excel_date?
Is this a variable or is this jus plain SQL against SQLite?

  

Bart,

excel_date is just a normal SQL alias name for the complicated 
expression. The string 'execl_date' will be returned as the name of this 
result column. This name is only used if you look at or display the 
column name, like you would if you use the sqlite shell to execute the 
command.


P.S. see my subsequent post after I noticed that your datefield was an 
integer raher than a string. Note that this string based version will 
also work because sqlite automatically converts the integer datefield 
value into a string to pass it to the substr function.


HTH

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Thanks, will try that.
What does the: % 100 do and what the Excel_date  ?

RBS

> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (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/ 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/  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.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

Thanks , will try that.
What is as excel_date?
Is this a variable or is this jus plain SQL against SQLite?

RBS

> RB Smissaert wrote:
>> When moving data from Interbase to SQLite I have to convert integer
>> dates in
>> the format mmdd to Excel dates. These are integer numbers counting
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (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/ 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/  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
>>
>>
> This should give you the excel integer date format directll.
>
> select
> cast (
> julianday(
> substr(datefield, 1, 4) || '-' ||
> substr(datefield, 5, 2) || '-' ||
> substr(datefield, 7,2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[Denis Povshedny]

Hi RBS!

In my application I use following approach:

It is a part of view to export csv data to Excel:

CREATE VIEW v_export_data_std AS
SELECT  localnumber, remotenumber, sipuser,
strftime( "%Y", timestart, 'unixepoch', 'localtime' ) AS
year,
strftime( "%m", timestart, 'unixepoch', 'localtime' ) AS
month,
strftime( "%d", timestart, 'unixepoch', 'localtime' ) AS
day,
strftime( "%H", timestart, 'unixepoch', 'localtime' ) AS
hour,
strftime( "%M", timestart, 'unixepoch', 'localtime' ) AS
minute,
strftime( "%S", timestart, 'unixepoch', 'localtime' ) AS
second,
timeend-timestart AS totalduration,
timeend-timeconnect AS talkduration
--  idscript, idisdn, idsip, state, disconnectreason
...
ORDER BY timestart;


These time* values are stored in a table with following definition:

timestart INTEGER NOT NULL CHECK( timestart > 0 ),
timeconnect INTEGER,
timeend INTEGER


And in most cases the 'timestart' field is set to current time, as a
number of second from 1/1/1970 epoch, something like 1154443669. 

For me this view is good enough to make any type of grouping and
comparing in Excel.

Regards, Denis


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: Sunday, December 03, 2006 8:32 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format mmdd


> Come to think of it, maybe I should make it mm/dd/ with the SQLite

> function substr().

That seems to work fine. So, when I have 20061203, meaning 3 December
2006 then if I make it 12/03/2006 with substr() then Excel picks it up
as the right date and I also can apply any date format. Still have the
problem though how to compare dates in SQLite when the format is the
integer mmdd. Maybe I will need some custom SQLite function.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 17:22
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format mmdd

I have an integer, but that is mmdd and has nil to do with the Excel
integer date format. Did you try putting a value in the integer format
mmdd in Excel and have it as a normal Excel date? Or did you try it
with a string in the format dd/mm/? Come to think of it, maybe I
should make it mm/dd/ with the SQLite function substr.

RBS

-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 17:11
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format mmdd

Still don't understand the issue.  Excel stores the date internally as
the integer as you describe, no matter how you chose to format the
cell's display.  Changing the display format does not change the cell's
internal format.  And to the best of my knowledge changing Excel's
internal cell storage structure is not possible.

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, December 03, 2006 8:56 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Dealing with dates in the format mmdd
>
>
> I am familiar with Excel and I have tried that but it doesn't work. I 
> find the only reliable way to put dates in Excel is to put the integer
> Excel date in like for example 39054 and then set the date
> format in the
> sheet.
>
> 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 mmdd
>
> Is there a reason you can use Excel's "Format Cells" to accomplish 
> what you wish? Enter a "Custom" format of "\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 mmdd
> >
> >
> > When moving data from Interbase to SQLite I have to convert integer 
> > dates in the format mmdd to Excel dates. These are integer 
> > numbers counting the
> > days past 31 December 1899. With substr I can make it
> > dd/mm/ (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/ if that would be a
> > possible date.
> > This is due to the US date format of Excel.
> > So, would it be pos

Re: [sqlite] Dealing with dates in the format yyyymmdd

[Dennis Cote]

RB Smissaert wrote:

When moving data from Interbase to SQLite I have to convert integer dates in
the format mmdd to Excel dates. These are integer numbers counting the
days past 31 December 1899. With substr I can make it dd/mm/ (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/ 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/  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

  

This should give you the excel integer date format directll.

   select
   cast (
   julianday(
   substr(datefield, 1, 4) || '-' ||
   substr(datefield, 5, 2) || '-' ||
   substr(datefield, 7,2)
   ) - julianday('1900-01-01')
   as integer
   ) as excel_date
   from mytable;

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[bartsmissaert]

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 mmdd) 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 mmdd
>>
>> 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 mmdd
>>>
>>>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 / 1 AS INT) * 1))
>>>> - "
>>>
>>>&
>>>
>>>>_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS
>>>> INT)
>>>>AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1
>>>> AS
>>>>INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1
>>>> 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 mmdd
>>>>
>>>>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 mmdd
>>>>
>>>>Why not use Interbase to transform the date into a character string,
>>>>like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite
>>>>date function to insert it into Sqlite?
>>>

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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 mmdd

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 mmdd

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 / 1 AS INT) * 1)) - "


&


_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd

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 mmdd

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
1) \ 100) * 100)) + _
   (153 * (((lIBDate - (lIBDate \ 1) * 1) \
100) + _
   12 * ((14 - ((lIBDate - (lIBDate \


1)




* 1) \ 100)) / 12) - 3) + 2) / 5 + _
   ((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
   365 + ((lIBDate \ 1) + 4800 - _
  ((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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 mmdd

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 S

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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 mmdd

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 mmdd
> 
> 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 / 1 AS INT) * 1)) - "
> 
> &
> 
>>_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
>>AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
>>INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd
>>
>>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 mmdd
>>
>>Why not use Interbase to transform the date into a character string, 
>>like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day
>>>
>>>
>>>I suppose I could something like this in SQL:
>>>
>>>((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
>>>1) \ 100) * 100)) + _
>>> (153 * (((lIBDate - (lIBDate \ 1) * 1) \
>>>100) + _
>>> 12 * ((14 - ((lIBDate - (lIBDate \
>>
>>1)
>>
>>
>>>* 1) \ 100)) / 12) - 3) + 2) / 5 + _
>>> ((lIBDate \ 1) + 4800 - ((14 -
>>>((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
>>>                 365 + ((lIBDate \ 1) + 4800 - _
>>>((14 - ((lIBDate - (lIBDate \
>>>1) * 1) \ 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.

RE: [sqlite] Dealing with dates in the format yyyymmdd

[Fred Williams]

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 mmdd
>
>
> 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 / 1 AS INT)
> * 1)) - " &
> _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) *
> 1) AS INT)
> AS INT) || '/' || " &  "CAST((E.ADDED_DATE -
> CAST(E.ADDED_DATE / 1 AS
> INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE
> / 1 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 mmdd
>
> 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 mmdd
>
> Why not use Interbase to transform the date into a character string,
> like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day
> >
> >
> > I suppose I could something like this in SQL:
> >
> > ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate -
> (lIBDate \ 1) *
> > 1) \ 100) * 100)) + _
> >   (153 * (((lIBDate - (lIBDate \
> 1) * 1) \
> > 100) + _
> >   12 * ((14 - ((lIBDate - (lIBDate \
> 1)
> > * 1) \ 100)) / 12) - 3) + 2) / 5 + _
> >   ((lIBDate \ 1) + 4800 - ((14 -
> > ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
> >   365 + ((lIBDate \ 1)
> + 4800 - _
> >  ((14 - ((lIBDate -
> (lIBDate \
> > 1) * 1) \ 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 mmdd
> >
> > 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 desc

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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 mmdd

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 / 1 AS INT) * 1)) - "


&


_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd

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 mmdd

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
1) \ 100) * 100)) + _
(153 * (((lIBDate - (lIBDate \ 1) * 1) \
100) + _
12 * ((14 - ((lIBDate - (lIBDate \


1)



* 1) \ 100)) / 12) - 3) + 2) / 5 + _
((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
365 + ((lIBDate \ 1) + 4800 - _
   ((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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 mmdd

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 2

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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 mmdd

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 / 1 AS INT) * 1)) - "
&
> _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
> AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
> INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd
> 
> 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 mmdd
> 
> Why not use Interbase to transform the date into a character string, 
> like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day
>>
>>
>>I suppose I could something like this in SQL:
>>
>>((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
>>1) \ 100) * 100)) + _
>>  (153 * (((lIBDate - (lIBDate \ 1) * 1) \
>>100) + _
>>  12 * ((14 - ((lIBDate - (lIBDate \
> 
> 1)
> 
>>* 1) \ 100)) / 12) - 3) + 2) / 5 + _
>>  ((lIBDate \ 1) + 4800 - ((14 -
>>((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
>>  365 + ((lIBDate \ 1) + 4800 - _
>> ((14 - ((lIBDate - (lIBDate \
>>1) * 1) \ 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 mmdd
>>
>>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 

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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 / 1 AS INT) * 1)) - " &
_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd

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 mmdd

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
1) \ 100) * 100)) + _
 (153 * (((lIBDate - (lIBDate \ 1) * 1) \
100) + _
 12 * ((14 - ((lIBDate - (lIBDate \


1)


* 1) \ 100)) / 12) - 3) + 2) / 5 + _
 ((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
 365 + ((lIBDate \ 1) + 4800 - _
((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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 mmdd

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 mmdd

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 ele

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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 / 1 AS INT) * 1)) - " &
_ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT)
AS INT) || '/' || " &  "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS
INT) * 1) / 100 AS INT) || '/' || " &  "CAST(E.ADDED_DATE / 1 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 mmdd

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 mmdd

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day
> 
> 
> I suppose I could something like this in SQL:
> 
> ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
> 1) \ 100) * 100)) + _
>   (153 * (((lIBDate - (lIBDate \ 1) * 1) \
> 100) + _
>   12 * ((14 - ((lIBDate - (lIBDate \
1)
> * 1) \ 100)) / 12) - 3) + 2) / 5 + _
>   ((lIBDate \ 1) + 4800 - ((14 -
> ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
>   365 + ((lIBDate \ 1) + 4800 - _
>  ((14 - ((lIBDate - (lIBDate \
> 1) * 1) \ 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 mmdd
> 
> 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 mmdd
>>
>>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

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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 mmdd

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day
> 
> 
> I suppose I could something like this in SQL:
> 
> ((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
> 1) \ 100) * 100)) + _
>   (153 * (((lIBDate - (lIBDate \ 1) * 1) \
> 100) + _
>   12 * ((14 - ((lIBDate - (lIBDate \
1)
> * 1) \ 100)) / 12) - 3) + 2) / 5 + _
>   ((lIBDate \ 1) + 4800 - ((14 -
> ((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
>   365 + ((lIBDate \ 1) + 4800 - _
>  ((14 - ((lIBDate - (lIBDate \
> 1) * 1) \ 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 mmdd
> 
> 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 mmdd
>>
>>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.

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

Why not use Interbase to transform the date into a character string, 
like an ISI8601 date or yymmdd, dd-mm- 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 * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
1) \ 100) * 100)) + _
  (153 * (((lIBDate - (lIBDate \ 1) * 1) \
100) + _
  12 * ((14 - ((lIBDate - (lIBDate \ 1)
* 1) \ 100)) / 12) - 3) + 2) / 5 + _
  ((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
  365 + ((lIBDate \ 1) + 4800 - _
 ((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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 mmdd

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 mmdd

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 mmdd. 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 mmdd

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.

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

> It looks like Interbase uses a traditional date format based on an epoch.

It just does year * 1 + month * 100 + day


I suppose I could something like this in SQL:

((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) *
1) \ 100) * 100)) + _
  (153 * (((lIBDate - (lIBDate \ 1) * 1) \
100) + _
  12 * ((14 - ((lIBDate - (lIBDate \ 1)
* 1) \ 100)) / 12) - 3) + 2) / 5 + _
  ((lIBDate \ 1) + 4800 - ((14 -
((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _
  365 + ((lIBDate \ 1) + 4800 - _
 ((14 - ((lIBDate - (lIBDate \
1) * 1) \ 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 mmdd

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 mmdd
> 
> 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 mmdd. 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 mmdd
>>
>>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 

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

Worked out a function to convert integer mmdd to an Excel date:

Function IBDates2ExcelDates(lIBDate As Long) As Long

   Dim lYear As Long
   Dim lMonth As Long
   Dim lDay As Long
   Dim a As Long
   Dim y As Long
   Dim m As Long
   Dim lJulianDay As Long

   lYear = lIBDate \ 1
   lMonth = (lIBDate - lYear * 1) \ 100
   lDay = lIBDate - (lYear * 1 + lMonth * 100)

   a = (14 - lMonth) / 12
   y = lYear + 4800 - a
   m = lMonth + 12 * a - 3

   lJulianDay = lDay + (153 * m + 2) / 5 + y * 365 + y / 4 - 32083
   
   IBDates2ExcelDates = lJulianDay - 2415033

End Function

Seems to work well. I know there is a bug in the Excel date where somewhere
shortly after 30 December 1899 it is one day out, but that is OK.

And I suppose this is something I can do in SQLite?
If so should I just put this in the query statement or can I somehow make a
UDF?

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 mmdd

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 mmdd. 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 mmdd
> 
> 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
>>mmdd 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 mmdd 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 mmdd 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 mmdd
>>
>>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 mmdd
>>
>>Is there a reason you can use Excel's "Format Cells" to accomplish what
>>you wish? Enter a "Custom" format of "\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: [sql

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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 mmdd

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 mmdd. 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 mmdd

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

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 p

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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

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 mmdd. 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 mmdd
> 
> 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
>>mmdd 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 mmdd 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 mmdd 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 mmdd
>>
>>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 mmdd
>>
>>Is there a reason you can use Excel's "Format Cells" to accomplish what
>>you wish? Enter a "Custom" format of "\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: 

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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 mmdd. 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 mmdd

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

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd


When moving data from Interbase to SQLite I have to convert
integer dates in
the format mmdd to Excel dates. These are integer numbers
counting the
days past 31 December 1899. With substr I can make it
dd/mm/ (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/ 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/  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]






-







--

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

> Come to think of it, maybe I should make it mm/dd/ with
> the SQLite  function substr().

That seems to work fine. So, when I have 20061203, meaning 3 December 2006
then if I make it 12/03/2006 with substr() then Excel picks it up as the
right date and I also can apply any date format.
Still have the problem though how to compare dates in SQLite when the format
is the integer mmdd. Maybe I will need some custom SQLite function.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 17:22
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd

I have an integer, but that is mmdd and has nil to do with the Excel
integer date format.
Did you try putting a value in the integer format mmdd in Excel and
have it as a normal Excel date? Or did you try it with a string in the
format dd/mm/? Come to think of it, maybe I should make it mm/dd/
with the SQLite function substr.

RBS

-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 17:11
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd

Still don't understand the issue.  Excel stores the date internally as
the integer as you describe, no matter how you chose to format the
cell's display.  Changing the display format does not change the cell's
internal format.  And to the best of my knowledge changing Excel's
internal cell storage structure is not possible.

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, December 03, 2006 8:56 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Dealing with dates in the format mmdd
>
>
> I am familiar with Excel and I have tried that but it doesn't work.
> I find the only reliable way to put dates in Excel is to put
> the integer
> Excel date in like for example 39054 and then set the date
> format in the
> sheet.
>
> 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 mmdd
>
> Is there a reason you can use Excel's "Format Cells" to
> accomplish what
> you wish? Enter a "Custom" format of "\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 mmdd
> >
> >
> > When moving data from Interbase to SQLite I have to convert
> > integer dates in
> > the format mmdd to Excel dates. These are integer numbers
> > counting the
> > days past 31 December 1899. With substr I can make it
> > dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

I have an integer, but that is mmdd and has nil to do with the Excel
integer date format.
Did you try putting a value in the integer format mmdd in Excel and
have it as a normal Excel date? Or did you try it with a string in the
format dd/mm/? Come to think of it, maybe I should make it mm/dd/
with the SQLite function substr.

RBS

-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 17:11
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format mmdd

Still don't understand the issue.  Excel stores the date internally as
the integer as you describe, no matter how you chose to format the
cell's display.  Changing the display format does not change the cell's
internal format.  And to the best of my knowledge changing Excel's
internal cell storage structure is not possible.

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, December 03, 2006 8:56 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Dealing with dates in the format mmdd
>
>
> I am familiar with Excel and I have tried that but it doesn't work.
> I find the only reliable way to put dates in Excel is to put
> the integer
> Excel date in like for example 39054 and then set the date
> format in the
> sheet.
>
> 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 mmdd
>
> Is there a reason you can use Excel's "Format Cells" to
> accomplish what
> you wish? Enter a "Custom" format of "\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 mmdd
> >
> >
> > When moving data from Interbase to SQLite I have to convert
> > integer dates in
> > the format mmdd to Excel dates. These are integer numbers
> > counting the
> > days past 31 December 1899. With substr I can make it
> > dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

Trouble is I need to import dates from Interbase that have the integer
format mmdd. 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 mmdd

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
> mmdd 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 mmdd 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 mmdd 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 mmdd
> 
> 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 mmdd
> 
> Is there a reason you can use Excel's "Format Cells" to accomplish what
> you wish? Enter a "Custom" format of "\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 mmdd
>>
>>
>>When moving data from Interbase to SQLite I have to convert
>>integer dates in
>>the format mmdd to Excel dates. These are integer numbers
>>counting the
>>days past 31 December 1899. With substr I can make it
>>dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[Fred Williams]

Still don't understand the issue.  Excel stores the date internally as
the integer as you describe, no matter how you chose to format the
cell's display.  Changing the display format does not change the cell's
internal format.  And to the best of my knowledge changing Excel's
internal cell storage structure is not possible.

Fred

> -Original Message-
> From: RB Smissaert [mailto:[EMAIL PROTECTED]
> Sent: Sunday, December 03, 2006 8:56 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Dealing with dates in the format mmdd
>
>
> I am familiar with Excel and I have tried that but it doesn't work.
> I find the only reliable way to put dates in Excel is to put
> the integer
> Excel date in like for example 39054 and then set the date
> format in the
> sheet.
>
> 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 mmdd
>
> Is there a reason you can use Excel's "Format Cells" to
> accomplish what
> you wish? Enter a "Custom" format of "\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 mmdd
> >
> >
> > When moving data from Interbase to SQLite I have to convert
> > integer dates in
> > the format mmdd to Excel dates. These are integer numbers
> > counting the
> > days past 31 December 1899. With substr I can make it
> > dd/mm/ (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/ 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/  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]
-

Re: [sqlite] Dealing with dates in the format yyyymmdd

[John Stanton]

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

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd


When moving data from Interbase to SQLite I have to convert
integer dates in
the format mmdd to Excel dates. These are integer numbers
counting the
days past 31 December 1899. With substr I can make it
dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

Just thought of one reason why it I need something else in SQLite than
mmdd 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 mmdd 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 mmdd 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 mmdd

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd
>
>
> When moving data from Interbase to SQLite I have to convert
> integer dates in
> the format mmdd to Excel dates. These are integer numbers
> counting the
> days past 31 December 1899. With substr I can make it
> dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd
>
>
> When moving data from Interbase to SQLite I have to convert
> integer dates in
> the format mmdd to Excel dates. These are integer numbers
> counting the
> days past 31 December 1899. With substr I can make it
> dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

I am familiar with Excel and I have tried that but it doesn't work.
I find the only reliable way to put dates in Excel is to put the integer
Excel date in like for example 39054 and then set the date format in the
sheet.

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 mmdd

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd
>
>
> When moving data from Interbase to SQLite I have to convert
> integer dates in
> the format mmdd to Excel dates. These are integer numbers
> counting the
> days past 31 December 1899. With substr I can make it
> dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[Fred Williams]

Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\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 mmdd
>
>
> When moving data from Interbase to SQLite I have to convert
> integer dates in
> the format mmdd to Excel dates. These are integer numbers
> counting the
> days past 31 December 1899. With substr I can make it
> dd/mm/ (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/ 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/  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]
-

RE: [sqlite] Dealing with dates in the format yyyymmdd

[RB Smissaert]

Forgot to say that I could stick a single quote in front of the dates in
SQLite and that would prevent the US date format, but it means I have no
dates anymore in the sheet, but strings, so I can't sort properly and I
can't do date calculations.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 03 December 2006 14:21
To: sqlite-users@sqlite.org
Subject: [sqlite] Dealing with dates in the format mmdd

When moving data from Interbase to SQLite I have to convert integer dates in
the format mmdd to Excel dates. These are integer numbers counting the
days past 31 December 1899. With substr I can make it dd/mm/ (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/ 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/  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]
-