SQLite DATETIME() Function

The SQLite DATETIME() function returns the date/time as text in 'YYYY-MM-DD HH:MM:SS' format.

Syntax

DATETIME(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/time value as text in 'YYYY-MM-DD HH:MM:SS' format.

Example: Current date and time

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

SELECT DATETIME('now');
Result: '2022-04-07 17:04:32'

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 DATETIME('2018-08-18 08:23:19', 'start of month');
Result: '2018-08-01 00:00:00'

SELECT DATETIME('now', 'start of month');
Result: '2022-04-01 00:00:00'

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 DATETIME('2018-08-18', 'start of month', '+1 month', '-1 day');
Result: '2018-08-31 00:00:00'

SELECT DATETIME('now', 'start of month', '+1 month', '-1 day');
Result: '2022-04-30 00:00:00'

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 DATETIME('2018-08-18 08:23:19', '+2 years');
Result: '2020-08-18 08:23:19'

SELECT DATETIME('2018-08-18 08:23:19', '-2 years');
Result: '2016-08-18 08:23:19'

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 DATETIME('2018-08-18 08:23:19', '+10 days');
Result: '2018-08-28 08:23:19'

SELECT DATETIME('2018-08-18 08:23:19', '-10 days');
Result: '2018-08-08 08:23:19'

Example: Adding/Subtracting hours

To add/subtract hours from a time-value, [+-]NNN hours modifier can be used. In the example below 5 hours is added and subtracted from a given time-value.

SELECT DATETIME('2018-08-18 08:23:19', '+5 hours');
Result: '2018-08-18 13:23:19'

SELECT DATETIME('2018-08-18 08:23:19', '-5 hours');
Result: '2018-08-18 03:23:19'

Example: Adding/Subtracting minutes

To add/subtract minutes from a time-value, [+-]NNN minutes modifier can be used. In the example below 30 minutes is added and subtracted from a given time-value.

SELECT DATETIME('2018-08-18 08:23:19', '+30 minutes');
Result: '2018-08-18 08:53:19'

SELECT DATETIME('2018-08-18 08:23:19', '-30 minutes');
Result: '2018-08-18 07:53:19'

❮ SQLite Functions