Sqlite where statement not working on datetime field

August 29, 2023

I have a SQLite database that uses a field called DateTime. In the code, I use DateTime.Now so this is never just a basic date it always is the current time that the record was entered.

When I try to run sql statement that looks like:

SELECT 
* 
FROM Tournament 
WHERE DateTime >= '8/1/2023 12:00:00 AM' AND DateTime <= '8/31/2023 11:59:59 PM'

This is the table

CREATE TABLE IF NOT EXISTS Tournament (
       Id TEXT PRIMARY KEY NOT NULL,
       Name TEXT,
       SiteId Text,
       DateTime Text NOT NULL,
       BuyIn REAL NOT NULL,
       Staking REAL NOT NULL,
       IsRebuy BOOLEAN NOT NULL,
       RebuyCount INTEGER,
       IsDeal BOOLEAN NOT NULL,
       IsAddOn BOOLEAN NOT NULL,
       FOREIGN KEY (SiteId) REFERENCES Site(Id)
 );

It yeilds 0 result, but when I Select * From Tournament I get 1 record with a field DateTime of this 2023-08-27 09:19:32.7331808

Can someone please help with this query?

>Solution :

Use the ISO601 format, not just in SQLite but any database and language.

WHERE DateTime >= '2023-08-01' AND DateTime < '2023-09-01'

Almost all languages and databases have explicit date types and automatically recognize the ISO8601 format except SQLite. In SQLite, dates that are stored as text should follow the ISO8601 format.

From the Data Types docs

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.