Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Date and Time Functions

SQL date modifiers

Reference for SQLite date modifiers such as +1 day, -3 months, start of month, weekday and unixepoch: how they combine, month-end pitfalls and common recipes.

Modifiers are the extra arguments you pass to SQLite's DATE(), TIME(), DATETIME(), JULIANDAY(), UNIXEPOCH() and STRFTIME() to shift or adjust a date. Add a number of days, jump to the start of the month, move forward to the next Monday, convert from Unix time: each modifier is a small text instruction, and they are applied from left to right, each acting on the result of the one before.

Date arithmetic in SQLite is done with modifiers because it has no DATEADD() and no INTERVAL. A handful of patterns cover almost every real need.

SQL
SELECT DATE('2025-02-14', '+1 month', 'start of month', '-1 day');   -- 2025-02-28

Try it

SQL Shifting and snapping dates

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

SQL Using modifiers in filters ยท database: company

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

SQL Month-end pitfalls

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

Syntax

SQL
DATE(time_value, modifier [, modifier, ...])
Modifier Effect
'+N days', '-N days' Add or subtract days (also hours, minutes, seconds, months, years; singular names work too)
'start of day', 'start of month', 'start of year' Reset to the start of that unit
'weekday N' Advance to the next date whose weekday is N (0 = Sunday ... 6 = Saturday); stays put if the date already matches
'unixepoch' Treat a number as Unix seconds (must come right after the value)
'julianday', 'auto' Treat a number as a Julian day; guess between the two
'localtime', 'utc' Convert from UTC to local time, or back
'subsec' Keep fractions of a second in the result
'ceiling', 'floor' Control how overflowing month and year arithmetic resolves (SQLite 3.46 and later)

N may be fractional ('+1.5 hours'). A modifier that is spelled wrongly makes the whole result NULL.

Dialect differences

Database Date arithmetic
SQLite Modifiers, as above.
PostgreSQL date + INTERVAL '1 month'; date_trunc('month', d) for "start of".
MySQL DATE_ADD(d, INTERVAL 1 MONTH); first of month with DATE_FORMAT(d, '%Y-%m-01').
SQL Server DATEADD(month, 1, d); EOMONTH(d) for the last day; DATETRUNC(month, d) in 2022+.
Oracle ADD_MONTHS(d, 1), TRUNC(d, 'MM'), LAST_DAY(d).

PostgreSQL, MySQL, SQL Server and Oracle clamp month-end overflow (January 31 plus one month is February 28 or 29). SQLite rolls forward instead.

Usage notes

  • Order is meaningful. '+1 month', '+1 day' is not the same as '+1 day', '+1 month' when a month end is involved.
  • Month-end overflow. DATE('2025-01-31', '+1 month') is 2025-03-03, because February 31 does not exist. Use the start-of-month pattern to reach a month end reliably.
  • First of next month: DATE(d, 'start of month', '+1 month'). Last day of this month: add '-1 day' to it.
  • Half-open ranges avoid time-of-day bugs: >= start AND < next_start beats BETWEEN on timestamps. See the second example.
  • Rolling windows: WHERE created_at >= DATETIME('now', '-30 days') for "the last 30 days".
  • 'weekday N' only moves forward (or stays). To get the previous Monday, step back a week first: DATE(d, '-6 days', 'weekday 1').
  • 'localtime' and 'utc' depend on the machine's time zone, so results differ between servers.