Date() Function in SQLite

by

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.