Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT DATE('2025-02-14 20:57:00');   -- 2025-02-14

Try it

SQL Cutting the time off a timestamp ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Accepted inputs, normalizing and invalid dates

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.)
SQL
-- 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') is 2025-03-02. Day 32 and month 13 give NULL. Use a CHECK (DATE(col) = col) constraint to reject dates that do not round-trip.
  • A plain number is a Julian day. DATE(1739566620) is NULL because 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 on ordered_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:00 late evening can land on the same UTC day.
  • The result is text, so it sorts and compares correctly only in ISO format.