CAST to DATE Returns the Number 2024 on SQLite Because It Has No Date Type

Filtering orders with CAST(created_at AS DATE) = '2024-05-06' works on PostgreSQL, MySQL and MariaDB. SQLite has no date type, turns the CAST into a number and keeps only the year, so the filter never matches.

SQLite keeps dates as text or numbers and reads a CAST to DATE, DATETIME or TIMESTAMP as a conversion to a number, which keeps only the leading digits. MySQL and MariaDB also refuse CAST(x AS TIMESTAMP), and PostgreSQL refuses DATETIME.

What Each Engine Answered

The example

SELECT CAST('2024-05-06' AS DATE);

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 4 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

This form answers the same on every engine:

SELECT DATE('2024-05-06');

DATE(x) gives a date on PostgreSQL, MySQL and MariaDB and ISO text (2024-05-06) on SQLite, which sorts and compares the same way.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: SQLite has no date type: CAST('2024-05-06' AS DATE) is the number 2024 there and a date on the other engines. [cast-to-date]
    CAST('2024-05-06' AS DATE)
    DATE(x) gives a date on PostgreSQL, MySQL and MariaDB and ISO text (2024-05-06) on SQLite, which sorts and compares the same way.

1 problem: 1 warning.

baresql rules cast-to-date prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.