SQLite date() Function

The SQLite date() function converts a time value specified by a time value and modifiers to a date string in YYYY-MM-DD format.

Syntax

Here is the syntax of the SQLite date() function:

date(time_value [, modifier, modifier, ...])

Parameters

time_value

Optional. A time value. The time value can be in any of the following formats, as shown below. The value is usually a string, but in the case of format 12 it can be an integer or a floating point number.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now - the current date and time
12. DDDDDDDDDD.dddddd - Julian days number with fractional part

modifier

Optional. You can use zero or more modifiers to change the time value time_value. Multiple modifiers are applied sequentially from left to right. You can use modifiers like this:

1. NNN days- Add NNN days to the time value
2. NNN hours- Add NNN hours to the time value
3. NNN minutes- Add NNN minutes to the time value
4. NNN.NNNN seconds- Add NNN.NNNN seconds to the time value
5. NNN months- Add NNN months to the time value
6. NNN years- Add NNN years to the time value
7. start of month - Move the time value to the start of its month.
8. start of year - Move the time value to the start of its year.
9. start of day - Move the time value to the start of its day.
10. weekday N - Advance to weekday N (Sunday is 0); if already on that day, leave the date unchanged.
11. unixepoch - Interpret the immediately preceding numeric time value as Unix seconds.
12. julianday - Interpret the immediately preceding numeric time value as a Julian day number.
13. auto - Interpret a numeric time value as a Julian day number or Unix timestamp based on its magnitude; Unix timestamps in the first 63 days of 1970 are ambiguous.
14. localtime - Assume the preceding time value is UTC and convert it to local time.
15. utc - Assume the preceding time value is local time and convert it to UTC.
16. subsec or subsecond (SQLite 3.42.0+) - Return fractional seconds from time(), datetime(), unixepoch(), and strftime('%s'); no effect on date() or julianday().
17. ceiling and floor (SQLite 3.46.0+) - Resolve ambiguous month/year shifts; ceiling is the default, while floor chooses the last day of the previous month.

The NNN represents a number. Can be a positive or negative number. If NNN is negative, it means subtraction.

The unixepoch, julianday, and auto modifiers must immediately follow the initial numeric time value. See the SQLite date and time function documentation for ordering rules and edge cases.

Return value

The SQLite date() function returns a date string in YYYY-MM-DD format. If no arguments are provided, the date() function returns the current date.

Examples

Here are examples that show how to use the SQLite date() function. When the time-value is omitted or set to 'now', SQLite uses the current UTC date, so the exact result changes over time.

• Get the current date using the SQLite date() function:

  SELECT date();
  

  Alternatively, you can use the SQLite date() function with a time value 'now' to get the current date:

  SELECT date('now');
  

• Get the first day of the year containing 2022-07-26:

  SELECT date('2022-07-26', 'start of year');
  

  date('2022-07-26', 'start of year')
  ----------------------------------
  2022-01-01

• Get the last day of the year containing 2022-07-26:

  SELECT date('2022-07-26', 'start of year', '1 year', '-1 day');
  

  date('2022-07-26', 'start of year', '1 year', '-1 day')
  ------------------------------------------------------
  2022-12-31

• Get the second Sunday in May for 2022:

  SELECT date('2022-01-01', '4 months', 'weekday 0', '7 days');
  

  date('2022-01-01', '4 months', 'weekday 0', '7 days')
  -----------------------------------------------------
  2022-05-08

  We know that Mother’s Day is the second Sunday in May every year.

  Start with the date '2022-01-01', then apply the modifiers '4 months', 'weekday 0', and '7 days' in order:

  1. '4 months' - Add four months to 2022-01-01, producing 2022-05-01.
  2. 'weekday 0' - Advance to Sunday. Since 2022-05-01 is a Sunday, the date stays the same.
  3. '7 days' - Add seven days to reach the second Sunday in May, 2022-05-08.