On 02/09/07, C M <[EMAIL PROTECTED]> wrote: > Hi, I'm very new to SQLite, and I'm using it with Python. > > I want to have queries that will match dates but not care about times. . . .
Hi, If your dates are stored in julian day (real) format, then the fractional part will indicate the time of day. This can be removed with the 'round' function. Thus: sqlite> create table tstTbl( a integer primary key, tstDate real ); sqlite> sqlite> insert into tstTbl( tstDate ) values( julianday('now') ); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:12:12' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-11-01 12:13:12' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:15:12' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:14:12' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 12:14:13' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 11:14:13' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 13:14:13' )); sqlite> insert into tstTbl( tstDate ) values( julianDay('2007-10-01 14:14:13' )); sqlite> sqlite> select *, dateTime( tstDate ) from tstTbl order by tstDate; 1|2454345.90499024|2007-09-02 09:43:11 7|2454374.96820602|2007-10-01 11:14:13 2|2454375.00847222|2007-10-01 12:12:12 5|2454375.00986111|2007-10-01 12:14:12 6|2454375.00987269|2007-10-01 12:14:13 4|2454375.01055556|2007-10-01 12:15:12 8|2454375.05153935|2007-10-01 13:14:13 9|2454375.09320602|2007-10-01 14:14:13 3|2454406.00916667|2007-11-01 12:13:12 sqlite> sqlite> select *, dateTime( tstDate ) from tstTbl where ...> cast(round(tstDate) as integer)= ...> cast(round(julianday('2007-11-01')) as integer); 3|2454406.00916667|2007-11-01 12:13:12 sqlite> sqlite> select *, dateTime( tstDate ) from tstTbl where ...> cast(round(tstDate) as integer)= ...> cast(round(julianday('2007-10-01')) as integer); 2|2454375.00847222|2007-10-01 12:12:12 4|2454375.01055556|2007-10-01 12:15:12 5|2454375.00986111|2007-10-01 12:14:12 6|2454375.00987269|2007-10-01 12:14:13 7|2454374.96820602|2007-10-01 11:14:13 8|2454375.05153935|2007-10-01 13:14:13 9|2454375.09320602|2007-10-01 14:14:13 sqlite> sqlite> select *, dateTime( tstDate ) from tstTbl where ...> cast(round(tstDate) as integer)= ...> cast(round(julianday('2007-09-02')) as integer); 1|2454345.90499024|2007-09-02 09:43:11 sqlite> Rgds, Simon ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------