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 |
| ||||||||||||||||||||||||||||||||
modifier, modifier, ... |
|
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