SQLite Tutorial SQLite Advanced SQLite Database SQLite References

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-valueDescription
YYYY-MM-DDDate value formatted as 'YYYY-MM-DD'
YYYY-MM-DD HH:MMDate value formatted as 'YYYY-MM-DD HH:MM'
YYYY-MM-DD HH:MM:SSDate value formatted as 'YYYY-MM-DD HH:MM:SS'
YYYY-MM-DD HH:MM:SS.SSSDate value formatted as 'YYYY-MM-DD HH:MM:SS.SSS'
YYYY-MM-DDTHH:MMDate value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and the time
YYYY-MM-DDTHH:MM:SSDate 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.SSSDate value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and the time
HH:MMDate value formatted as 'HH:MM'
HH:MM:SSDate value formatted as 'HH:MM:SS'
HH:MM:SS.SSSDate value formatted as 'HH:MM:SS.SSS'
nownow is a literal used to return the current date.
DDDDDDDDDDJulian 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:

modifierDescription
[+-]NNN daysNumber of days added/subtracted to the date
[+-]NNN hoursNumber of hours added/subtracted to the date
[+-]NNN minutesNumber of minutes added/subtracted to the date
[+-]NNN.NNNN secondsNumber of seconds (and fractional seconds) added/subtracted to the date
[+-]NNN monthsNumber of months added/subtracted to the date
[+-]NNN yearsNumber of years added/subtracted to the date
start of monthShifting the date back to the start of the month
start of yearShifting the date back to the start of the year
start of dayShifting the date back to the start of the day
weekday NMoves 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)
unixepochUsed with the DDDDDDDDDD format to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
juliandayUsed with the DDDDDDDDDD format to force the time-value number to be interpreted as a julian-day number
autoUsed with the DDDDDDDDDD format to interpret the time-value as either a julian day number or a unix timestamp, depending on its magnitude.
localtimeAdjusts date to localtime, assuming the time-value was expressed in UTC
utcAdjusts 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