cember 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 d
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 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
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 Inte
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 st
[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
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 worki
[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 (
...>
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 i
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_dat
[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
nume
> 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
> 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
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
>
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
[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 quoti
[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.
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. Wi
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)
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
GER,
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--
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)
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 an
in an Excel sheet.
RBS
-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: 04 December 2006 00:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd
I suspect that you are not using the Interbase date type, which is a 64
bit o
anton [mailto:[EMAIL PROTECTED]
Sent: 04 December 2006 00:41
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Dealing with dates in the format yyyymmdd
I suspect that you are not using the Interbase date type, which is a 64
bit object encapsulating date and time and having an epoch November 17,
ginal 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
&g
al 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
al 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 w
S
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
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,
-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
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
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 Interb
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
his 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 m
iven
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 "integ
lite-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 da
m/? 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
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 store
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
nternal 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
>
>
&g
ent: 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
&qu
ite-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 Willi
@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.
ECTED]
Sent: 03 December 2006 14:41
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Dealing with dates in the format yyyymmdd
Is there a reason you can use Excel's "Format Cells" to accomplish what
you wish? Enter a "Custom" format of "\mm\dd" in a cell
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
> -Or
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 [
47 matches
Mail list logo
