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- Fall back to the beginning of the month in time_value
  8. start of year- Fall back to the beginning of the year in time_value
  9. start of day- Fall back to the beginning of the day in time_value
  10. weekday N- Advance time_value ​​to weekday N
  11. unixepoch- Unix timestamp for now
  12. julianday- The Julian calendar days for now
  13. auto
  14. localtime- The current time
  15. utc- The utc time

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

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 some examples to show usages of the SQLite date() function.

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

    SELECT date();

    date()
----------
2022-07-26

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

    SELECT date('now');

    date('now')
-----------
2022-07-26

  • Use the SQLite date() function to get the first day of the current year:

    SELECT date('now', 'start of year');

    date('now', 'start of year')
----------------------------
2022-01-01

  • Use the SQLite date() function to get the last day of the current year:

    SELECT date('now', 'start of year', '1 year', '-1 day');

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

  • Use the SQLite date() function to get the current year’s Mother’s Day:

    SELECT date('now', 'start of year', '4 months', 'weekday 0', '7 days');

    date('now', 'start of year', '4 months', 'weekday 0', '7 day
------------------------------------------------------------
2022-05-08

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

    To get it, we use the time value 'now', and then use several modifiers: 'start of year', '4 months', 'weekday 0', '7 days'. The following is the calculating stpes for the date() function:

    1. 'now' - Get current time
    2. 'start of year' - Fall back the current time to the first day of the current year. That is 2022-01-01.
    3. '4 months' - Add 4 months from the first day of the year. That is 2022-05-01.
    4. 'weekday 0' - Get the first Sunday starting at 2022-05-01. That is2022-05-01.
    5. '7 days' - Add 7 days from the first Sunday, which is the second Sunday in May. The result is 2022-05-08.