SQL Reference ยท Date and Time Functions
SQL DATE() function
Reference for SQL DATE(): cut the date out of a date-time, normalize date text, apply modifiers, accepted input formats and the DATE() forms in other databases.
DATE(value, modifier, ...) returns the date part of a time value as text in the form YYYY-MM-DD. Use it to cut the time off a timestamp, to check that text really is a valid date, and, with modifiers, to do date arithmetic such as "30 days after hire".
SQLite has no separate date type, so DATE() works on ordinary text, Julian day numbers and Unix timestamps. The same name exists in MySQL, where it extracts the date from an expression, and in PostgreSQL and Oracle as a type or conversion; the details differ below.
SELECT DATE('2025-02-14 20:57:00'); -- 2025-02-14
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
DATE(time_value [, modifier, ...])
| Name | Description |
|---|---|
time_value |
A date or date-time as ISO text (YYYY-MM-DD, YYYY-MM-DD HH:MM[:SS[.SSS]], with T or a space; an optional zone offset such as +02:00 or Z), the word 'now', or a number (a Julian day, or Unix seconds with the 'unixepoch' modifier). |
modifier |
Optional, repeatable. Shifts or adjusts the date, for example '+1 month', 'start of year', 'weekday 1'. Applied left to right. See date modifiers. |
Returns: text YYYY-MM-DD, or NULL if the value cannot be understood or is NULL.
Dialect differences
| Database | Behavior |
|---|---|
| SQLite | DATE(value, modifiers...) as above; returns text. |
| PostgreSQL | No DATE() function; use CAST(ts AS date), ts::date or date_trunc('day', ts). date '2025-02-14' is a typed literal. |
| MySQL | DATE(expr) returns the date part of a date or datetime. Invalid values give NULL with a warning. Use DATE_ADD() for arithmetic. |
| SQL Server | No DATE() function; CAST(x AS date) or CONVERT(date, x). |
| Oracle | TRUNC(d) removes the time; TO_DATE('2025-02-14', 'YYYY-MM-DD') converts text. (Oracle's DATE type also holds a time.) |
-- PostgreSQL SELECT ordered_at::date FROM orders; -- MySQL SELECT DATE(ordered_at) FROM orders; -- SQL Server SELECT CAST(ordered_at AS date) FROM orders; -- Oracle SELECT TRUNC(ordered_at) FROM orders;
Usage notes
- Out-of-range days roll over. SQLite accepts day numbers up to 31 in any month, so
DATE('2025-02-30')is2025-03-02. Day32and month13giveNULL. Use aCHECK (DATE(col) = col)constraint to reject dates that do not round-trip. - A plain number is a Julian day.
DATE(1739566620)isNULLbecause it is out of range as a Julian day; add'unixepoch'. - Grouping by day.
GROUP BY DATE(ordered_at)is the usual way to build a daily report. - Filtering a day.
WHERE DATE(ordered_at) = '2025-02-14'is correct but cannot use an index onordered_at. A range (>= '2025-02-14' AND < '2025-02-15') can. - Time zones. An input offset is converted to UTC before the date is taken, so
+02:00late evening can land on the same UTC day. - The result is text, so it sorts and compares correctly only in ISO format.
Related
TIME(),DATETIME(),STRFTIME(),JULIANDAY(), date modifiers,CAST- Lessons: Date functions, Types and CAST