The SQLite DATE() function returns the date as text in 'YYYY-MM-DD' format.
Syntax
DATE(time-value, modifier, modifier, ...)
Parameters
time-value |
Required. Specify a time-value. It can be one of the following:
| time-value | Description |
|---|
| YYYY-MM-DD | Date value formatted as 'YYYY-MM-DD' |
| YYYY-MM-DD HH:MM | Date value formatted as 'YYYY-MM-DD HH:MM' |
| YYYY-MM-DD HH:MM:SS | Date value formatted as 'YYYY-MM-DD HH:MM:SS' |
| YYYY-MM-DD HH:MM:SS.SSS | Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS' |
| YYYY-MM-DDTHH:MM | Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and the time |
| YYYY-MM-DDTHH:MM:SS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and the time |
| YYYY-MM-DDTHH:MM:SS.SSS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and the time |
| HH:MM | Date value formatted as 'HH:MM' |
| HH:MM:SS | Date value formatted as 'HH:MM:SS' |
| HH:MM:SS.SSS | Date value formatted as 'HH:MM:SS.SSS' |
| now | now is a literal used to return the current date. |
| DDDDDDDDDD | Julian date number |
|
modifier, modifier, ... |
Optional. Specify modifiers. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right and are cumulative. The available modifiers are as follows:
| modifier | Description |
|---|
| [+-]NNN days | Number of days added/subtracted to the date |
| [+-]NNN hours | Number of hours added/subtracted to the date |
| [+-]NNN minutes | Number of minutes added/subtracted to the date |
| [+-]NNN.NNNN seconds | Number of seconds (and fractional seconds) added/subtracted to the date |
| [+-]NNN months | Number of months added/subtracted to the date |
| [+-]NNN years | Number of years added/subtracted to the date |
| start of month | Shifting the date back to the start of the month |
| start of year | Shifting the date back to the start of the year |
| start of day | Shifting the date back to the start of the day |
| weekday N | Moves the date forward to the next date where weekday number is N
(0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) |
| unixepoch | Used with the DDDDDDDDDD format to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01) |
| julianday | Used with the DDDDDDDDDD format to force the time-value number to be interpreted as a julian-day number |
| auto | Used with the DDDDDDDDDD format to interpret the time-value as either a julian day number or a unix timestamp, depending on its magnitude. |
| localtime | Adjusts date to localtime, assuming the time-value was expressed in UTC |
| utc | Adjusts date to utc, assuming the time-value was expressed in localtime |
|
Return Value
Returns the date value as text in 'YYYY-MM-DD' format.
Example: Current date
The SQLite 'now' time-value can be used to get the current date.
SELECT DATE('now');
Result: '2022-04-07'
Example: First day of the month
To get the first day of the month, 'start of month' modifier can be used. See the example below:
SELECT DATE('2018-08-18', 'start of month');
Result: '2018-08-01'
SELECT DATE('now', 'start of month');
Result: '2022-04-01'
Example: Last day of the month
To get the last day of the month, first, the 'start of month' modifier is used to calculate the start day of the month and then 1 month is added and then 1 day is subtracted.
SELECT DATE('2018-08-18', 'start of month', '+1 month', '-1 day');
Result: '2018-08-31'
SELECT DATE('now', 'start of month', '+1 month', '-1 day');
Result: '2022-04-30'
Example: Adding/Subtracting years
To add/subtract years from a time-value, [+-]NNN years modifier can be used. In the example below 2 years is added and subtracted from a given time-value.
SELECT DATE('2018-08-18', '+2 years');
Result: '2020-08-18'
SELECT DATE('2018-08-18', '-2 years');
Result: '2016-08-18'
Example: Adding/Subtracting days
To add/subtract years drom a time-value, [+-]NNN days modifier can be used. In the example below 10 days is added and subtracted from a given time-value.
SELECT DATE('2018-08-18', '+10 days');
Result: '2018-08-28'
SELECT DATE('2018-08-18', '-10 days');
Result: '2018-08-08'
❮ SQLite Functions
