SQLite Date Function To get a date value, the SQLite date function is used. It returns a date value in ‘YYYY-MM-DD’ format.
Syntax:
date( time_string, [ modifier1, modifier2, ... modifier_n ] )
Time_string:
| TIMESTRING | USES |
| now | To get the current date. |
| YYYY-MM-DD | To specify the date value formatted as ‘YYYY-MM-DD’. |
| YYYY-MM-DD HH:MM | To specify the date value formatted as ‘YYYY-MM-DD HH:MM’. |
| YYYY-MM-DD HH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS’. |
| YYYY-MM-DD HH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’. |
| HH:MM | To specify the date value formatted as ‘HH:MM’. |
| HH:MM:SS | To specify the date value formatted as ‘HH:MM:SS’. |
| HH:MM:SS.SSS | To specify the date value formatted as ‘HH:MM:SS.SSS’. |
| YYYY-MM-DDTHH:MM | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’. Here, T is a literal character used to separate the date and time portions. |
| YYYY-MM-DDTHH:MM:SS.SSS | To specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’. Here, T is a literal character used to separate the date and time portions. |
| DDDDDDDDDD | To specify the Julian date number. |
Modifier: Modifiers are used to add or subtract time, date or years.
| MODIFIER | USES |
| [+-]NNN years | Specifies the number of years added/subtracted to the date. |
| [+-]NNN months | Specifies the number of months added/subtracted to the date. |
| [+-]NNN days | Specifies the number of days added/subtracted to the date. |
| [+-]NNN hours | Specifies the number of hours added/subtracted to the date. |
| [+-]NNN minutes | Specifies the number of minutes added/subtracted to the date. |
| [+-]NNN seconds | Specifies the number of seconds added/subtracted to the date. |
| [+-]NNN.NNNN seconds | Specifies the number of seconds (and fractional seconds) added/subtracted to the date. |
| start of year | Shifts the date back to the start of the year. |
| start of month | Shifts the date back to the start of the month. |
| start of day | Shifts the date back to the start of the day. |
| weekday N | Move 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 | Interprets the date as UNIX Time (ie: number of seconds since 1970-01-01). |
| localtime | Adjusts the date to localtime, assuming the time string was expressed in UTC. |
| utc | Adjusts the date to utc, assuming the time string was expressed in localtime. |
Example1:
SELECT date('now'); |
Output:
2019-08-05 |
Explanation: In the above example, we are retrieving the current date.
Example 2:
SELECT date('now', 'start of month'); |
Output:
2019-08-01 |
Explanation: In the above example, we are retrieving the first day of the month.
Example 3:
SELECT date('2019-08-05', 'start of month'); |
Output:
2019-08-01 |
Explanation: In the above example, we are retrieving the first day of the month.
Example 4:
SELECT date('now', 'start of month','+1 month', '-1 day'); |
Output:
2019-08-31 |
Explanation: In the above example, we are retrieving the last day of the month.
Example 5:
SELECT date('2019-08-05', 'start of month','+1 month', '-1 day'); |
Output:
2019-08-31 |
Explanation: In the above example, we are retrieving the last day of the month.
Example 6:
SELECT date('now','+2 years'); |
Output:
2021-08-05 |
Explanation: In the above example, we are adding 2 years to the current date.
Example 7:
SELECT date('2019-08-05','+2 years'); |
Output:
2021-08-05 |
Explanation: In the above example, we are adding 2 years to the specified date.
Example 8:
SELECT date('now','+2 days'); |
Output:
2019-08-07 |
Explanation: In the above example, we are adding 2 days to the current date.
Example 9:
SELECT date('2019-08-05','+2 days'); |
Output:
2019-08-07 |
Explanation: In the above example, we are adding 2 days to the specified date.
