How to get only the date from a datetime in SQL

1date(): keep only the dayRecommended

SQLite has no separate date or datetime type. A timestamp is usually stored as ISO-8601 text, 'YYYY-MM-DD HH:MM:SS', and the date functions parse that text. date(x) returns the 'YYYY-MM-DD' part. It accepts a T separator, a trailing Z and missing seconds, and with the 'unixepoch' modifier it reads a Unix timestamp stored as a number.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT);
INSERT INTO orders (placed_at) VALUES
    ('2026-03-14 09:30:00'),
    ('2026-03-14 23:05:41'),
    ('2026-03-15T07:12:09Z'),
    ('2026-03-16 18:45');

-- date() keeps the 'YYYY-MM-DD' part of any ISO-8601 timestamp
SELECT id, placed_at, date(placed_at) AS day
FROM orders;

-- The result is text, and it is NULL when the input isn't ISO-8601
SELECT typeof(date('2026-03-14 09:30:00')) AS type,
       date('14/03/2026 09:30') AS not_iso,
       date(1773480600, 'unixepoch') AS from_epoch;

All four formats come back as a plain day. The second query shows the edges: typeof reports text, because SQLite has no date type to return, and a day-first string such as '14/03/2026 09:30' gives NULL rather than an error. A column full of NULLs from date() usually means the dates are stored in a format SQLite doesn't parse. The Unix timestamp 1773480600 is 2026-03-14 09:30:00 UTC, so it lands on the same day. Because the result is year-month-day text, it sorts and compares correctly as a string.

2CAST(x AS DATE): the standard form, and why SQLite gets it wrong

In PostgreSQL, MySQL and SQL Server, CAST(placed_at AS date) is the usual answer, and it returns a real date. SQLite accepts the same syntax without complaint, but DATE isn't a type there. SQLite gives an unknown type name numeric affinity, so the cast reads the number at the start of the text and stops at the first -.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT);
INSERT INTO orders (placed_at) VALUES
    ('2026-03-14 09:30:00'),
    ('2026-03-15 07:12:09');

-- The standard form: other databases return a date here, SQLite doesn't
SELECT id,
       CAST(placed_at AS DATE) AS cast_date,
       typeof(CAST(placed_at AS DATE)) AS type,
       date(placed_at) AS day
FROM orders;

The cast returns 2026, an integer: only the year survives, and every row from 2026 looks the same. There is no error to warn you, so a query copied from a SQL Server or PostgreSQL answer silently groups a whole year into one row. In SQLite, use date(); everywhere else, CAST(x AS date) is the portable choice.

3Filter the rows from one day

WHERE placed_at = '2026-03-14' compares a full timestamp with a bare date, and they are never equal. WHERE date(placed_at) = '2026-03-14' works, but the database has to compute date() for every row before it can compare, so an index on placed_at goes unused. A half-open range on the raw column gives the same rows and lets the index do the work. EXPLAIN QUERY PLAN shows the difference.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT);
CREATE INDEX idx_placed ON orders (placed_at);
INSERT INTO orders (placed_at) VALUES
    ('2026-03-13 22:10:00'),
    ('2026-03-14 00:00:00'),
    ('2026-03-14 09:30:00'),
    ('2026-03-14 23:59:59'),
    ('2026-03-15 00:00:00');

-- A timestamp never equals a bare date
SELECT count(*) AS n FROM orders WHERE placed_at = '2026-03-14';

-- Every row on 14 March: from midnight, up to but not including the 15th
SELECT id, placed_at
FROM orders
WHERE placed_at >= '2026-03-14' AND placed_at < '2026-03-15';

-- Same rows, but date() runs on every row, so the index can't help
EXPLAIN QUERY PLAN
SELECT id FROM orders WHERE date(placed_at) = '2026-03-14';

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE placed_at >= '2026-03-14' AND placed_at < '2026-03-15';

The equality test counts 0 rows. The range keeps rows 2 to 4, from exactly midnight to one second before the next midnight, and drops the rows from the 13th and the 15th. The two plans are the point: filtering on date(placed_at) gives SCAN orders, a read of the whole table, while the range gives SEARCH ... USING COVERING INDEX, a jump straight to the matching index entries. On five rows it makes no difference; on millions it does, and the same rule holds for CAST(x AS date) or DATE(x) in other databases. The upper bound is < the next day, not <= '2026-03-14 23:59:59', so fractional seconds such as 23:59:59.5 can't slip through.

4Group by day or by month

To count or sum per day, group by the date expression. For months, strftime formats the timestamp: '%Y-%m' gives '2026-03', and '%Y' gives the year. If you'd rather have a real day, the 'start of month' modifier turns any timestamp into the first of its month.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT, total INTEGER);
INSERT INTO orders (placed_at, total) VALUES
    ('2026-02-27 16:20:00', 18),
    ('2026-03-14 09:30:00', 42),
    ('2026-03-14 23:05:41', 12),
    ('2026-03-15 07:12:09', 30),
    ('2026-04-02 11:00:00', 9);

-- One row per day
SELECT date(placed_at) AS day, count(*) AS orders, sum(total) AS total
FROM orders
GROUP BY day
ORDER BY day;

-- One row per month: strftime, or date() with 'start of month'
SELECT strftime('%Y-%m', placed_at) AS month,
       date(placed_at, 'start of month') AS first_day,
       count(*) AS orders
FROM orders
GROUP BY month, first_day
ORDER BY month;

The two orders on 14 March fold into one row with a total of 54, and the month query puts all three March orders together. Days with no orders don't appear at all; filling the gaps needs a calendar of days to join against. SQLite, MySQL and PostgreSQL let GROUP BY use a column alias such as day; SQL Server doesn't, so there you repeat the expression, GROUP BY CAST(placed_at AS date).

5Get the time part instead

The other half works the same way. time(x) returns 'HH:MM:SS', strftime picks any parts you name, and casting strftime('%H', x) to INTEGER gives the hour as a number for arithmetic or bucketing. Because the time is zero-padded text, comparing it with '12:00:00' works too.

CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT);
INSERT INTO orders (placed_at) VALUES
    ('2026-03-14 09:30:00'),
    ('2026-03-14 23:05:41'),
    ('2026-03-15 07:12:09');

SELECT id,
       time(placed_at) AS time,
       strftime('%H:%M', placed_at) AS hh_mm,
       CAST(strftime('%H', placed_at) AS INTEGER) AS hour
FROM orders;

-- Orders placed before noon, on any day
SELECT id, placed_at
FROM orders
WHERE time(placed_at) < '12:00:00';

strftime('%H', ...) returns text such as '09'; the cast turns it into the number 9. The noon filter keeps orders 1 and 3, from two different days. In PostgreSQL, MySQL and SQL Server, CAST(x AS time) returns the time part as a time value.

6Other databases

The same jobs in PostgreSQL, MySQL and SQL Server. Each form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server's T-SQL (through Babelfish) with the timestamp 2026-03-14 09:30:00. Oracle's DATE type keeps a time of day, so there the usual answer is TRUNC(x), which sets the time to midnight.