strftime Function in SQLite

by

SQLite strftime Function

To fetch date and time, the SQLite strftime function is used. It is a very powerful function and is also used to perform date calculations.

 

Syntax:

strftime (format, timestring [, modifier1, modifier2, ... modifier_n ] )

 

Format:

FORMAT DESCRIPTION VALUES
Year 0000 to 9999
Week of year 00 to 53
Day of week 0 to 6, where 0 is Sunday
Month of year 01 to 12
Day of month 00 to 31
Hour 00 to 24
Minute 00 to 25
Seconds 00 to 59
Seconds Since 1970-01-01
Fractional seconds SS.SSS
Day of year 001 to 366
Julian day Numeric value

 

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.

 
Example 1: 

SELECT strftime('%Y %m %d', 'now');

Output: 

2019-08-05

Explanation:
In the above example, we are retrieving the current date.

Example 2: 

SELECT strftime('%Y-%m-%d', '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 strftime('%Y-%m-%d', '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 strftime('%Y-%m-%d', '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 strftime('%Y-%m-%d', '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 strftime('%Y-%m-%d', 'now','+2 years');

Output: 

2021-08-05

Explanation:
In the above example, we are adding 2 years to the current date.

Example 7: 

SELECT strftime('%Y-%m-%d', '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 strftime('%Y-%m-%d', 'now','+2 days');

Output: 

2019-08-07

Explanation:
In the above example, we are adding 2 days to the current date.

Example 9: 

SELECT strftime('%Y-%m-%d', '2019-08-05','+2 days');

Output: 

2019-08-07

Explanation:
In the above example, we are adding 2 days to the specified date.

Example 10: 

SELECT strftime('%Y-%m-%d', '2019-08-05','-2 days');

Output: 

2019-08-03

Explanation:
In the above example, we are subtracting 2 days from the specified date.