SQLite STRFTIME() Function

The SQLite STRFTIME() function returns the date formatted according to the format string specified as the first argument. It is modeled after the strftime() function from the standard C library but with some differences.

Syntax

STRFTIME(format, time-value, modifier, modifier, ...)

Parameters

Return Value

Returns the date formatted according to the specified format string.

Example: Current date and time

The SQLite 'now' time-value can be used to get the current date and time.

SELECT STRFTIME('%Y-%m-%d', 'now');
Result: '2022-04-12'

SELECT STRFTIME('%Y-%m-%d %H:%M', 'now');
Result: '2022-04-12 07:15'

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 STRFTIME('%Y-%m-%d', '2018-08-18', 'start of month');
Result: '2018-08-01'

SELECT STRFTIME('%Y-%m-%d', '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 STRFTIME('%Y-%m-%d', '2018-08-18', 'start of month', '+1 month', '-1 day');
Result: '2018-08-31'

SELECT STRFTIME('%Y-%m-%d', '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 STRFTIME('%Y-%m-%d', '2018-08-18', '+2 years');
Result: '2020-08-18'

SELECT STRFTIME('%Y-%m-%d', '2018-08-18', '-2 years');
Result: '2016-08-18'

Example: Adding/Subtracting days

To add/subtract days 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 STRFTIME('%Y-%m-%d', '2018-08-18', '+10 days');
Result: '2018-08-28'

SELECT STRFTIME('%Y-%m-%d', '2018-08-18', '-10 days');
Result: '2018-08-08'