----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 06/01/2004 08:34 AM -----
Ben Carlyle
06/01/2004 08:31 AM
To: "Allan Edwards" <[EMAIL PROTECTED]>@CORP
cc:
Subject: RE: [sqlite] Documentation
Hello,
"Allan Edwards" <[EMAIL PROTECTED]>
06/01/2004 12:18 AM
To: "'Roger Reghin'" <[EMAIL PROTECTED]>, "'Ralph Wetzel'"
<[EMAIL PROTECTED]>
cc: <[EMAIL PROTECTED]>
Subject: RE: [sqlite] Documentation
> CREATE TABLE Allan (DateTimeField DateTime);
> INSERT INTO Allan (DateTimeField) VALUES ('2004-01-04 12:00:00');
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) =
> datetime('2004-01-04 12:00:00');
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) >
> datetime('2004-01-03 12:00:00');
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) <
> datetime('2004-01-03 12:00:00');
Part of the appeal of this particular time and date format is that for
normal equality and comparison tests like the ones you've done you don't
need to call any datetime funcations. String comparison is fine (at least
until the year 10000 needs to be represented) because the digits are
always in exactly the same places in the string (which is why it's
important to still include the leading 0 chars):
SELECT DateTimeField FROM Allan WHERE DateTimeField = '2004-01-04
12:00:00';
SELECT DateTimeField FROM Allan WHERE DateTimeField > '2004-01-03
12:00:00';
SELECT DateTimeField FROM Allan WHERE DateTimeField < '2004-01-03
12:00:00';
By dropping the transformation you permit the current version of sqlite to
use any relevant index to do these selections.
I don't know the ANSI standard to which you referred, but XSD defines the
datetime with a few extra tweaks: A T between the date and time, optional
sub-second resolution, and an optional time-zone:
'2004-01-04T12:00:00+10:00'.
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
