Sign in Get started

SQLite 3 reference · Date and Time Functions

SQLite DATE() Function

Return a date as text in YYYY-MM-DD form, optionally shifted with modifiers like '+7 days' or 'start of month'.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

date(time-value, modifier, ...)

Parameters

ParameterDescription
time-value A date/time: 'YYYY-MM-DD', 'YYYY-MM-DD HH:MM:SS', 'now', a Julian day number, or a Unix time with the 'unixepoch' modifier.
modifiers optional Adjustments applied left to right, such as '+1 day', '-3 months', 'start of month', 'weekday 1', 'localtime', 'unixepoch'.

Return value

Text YYYY-MM-DD, or NULL for invalid input.

Examples

Dates and modifiersTry it yourself
SELECT date('2026-01-31 18:30:00') AS just_date,
       date('2026-01-31', '+7 days') AS week_later,
       date('2026-03-15', 'start of month') AS month_start;

Output

just_date | week_later | month_start
2026-01-31 | 2026-02-07 | 2026-03-01
Add days to a columnTry it yourself
SELECT order_id, order_date, date(order_date, '+14 days') AS return_by
FROM orders
LIMIT 3;

Output

order_id | order_date | return_by
1 | 2026-01-05 | 2026-01-19
2 | 2026-01-12 | 2026-01-26
3 | 2026-02-02 | 2026-02-16
Last day of the monthTry it yourself
SELECT date('2026-02-14', 'start of month', '+1 month', '-1 day') AS last_day_of_feb;

Output

last_day_of_feb
2026-02-28

SQLite has no DATE type: dates are stored as text ('2026-01-05'), numbers (Julian days) or Unix times, and these functions convert between them. Store dates as ISO-8601 text so they sort correctly.

In other languages

  • DATETIME() · Return a date and time as text in YYYY-MM-DD HH:MM:SS form, optionally shifted with modifiers.
  • TIME() · Return the time part of a date/time as text in HH:MM:SS form, optionally shifted with modifiers.
  • STRFTIME() · Format a date/time with % codes, such as %Y year, %m month, %d day, %H hour, %W week and %w weekday.
  • JULIANDAY() · Return the Julian day number (days since 4714 BC, with a fraction for the time). Subtract two to get days between dates.
  • SQL Date Functions · tutorial

Was this page helpful?