SQLite DateTime Function
To retrieve date and time in different formats, the DateTime function is used in SQLite. The result format of DateTime function is ‘YYYY-MM-DD HH:MM:SS’.
Syntax:
datetime(timestring, [ modifier1, modifier2, ... modifier_n ] )
Example 1:
SELECT datetime('now'); |
Output:
2019-08-05 12:00:07 |
Explanation:
In the above example, we are retrieving the current date and time.
Example 2:
SELECT datetime('now', '+2 years'); |
Output:
2021-08-05 12:00:07 |
Explanation:
In the above example, we are adding 2 years to the current date and time.
Example 3:
SELECT datetime('2019-08-05', '+2 years'); |
Output:
2021-08-05 00:00:00 |
Explanation:
In the above example, we are adding 2 years to the specified date and time.
Example 4:
SELECT datetime('now', '+2 days'); |
Output:
2019-08-07 12:00:07 |
Explanation:
In the above example, we are adding 2 days to the current date and time.
Example 5:
SELECT datetime('2019-08-05','+2 days'); |
Output:
2019-08-07 00:00:00 |
Explanation:
In the above example, we are adding 2 days to the specified date and time.
Example 6:
SELECT datetime('now','+2 hours'); |
Output:
2019-08-05 14:00:07 |
Explanation:
In the above example, we are adding 2 hours to the current date and time.
Example 7:
SELECT datetime('2019-08-05','+2 hours'); |
Output:
2019-08-05 02:00:00 |
Explanation:
In the above example, we are adding 2 hours to the specified date and time.
Example 8:
SELECT datetime('now','+2 minutes'); |
Output:
2019-08-05 12:02:07 |
Explanation:
In the above example, we are adding 2 minutes to the current date and time.
Example 9:
SELECT datetime('2019-08-05','+2 minutes'); |
Output:
2019-08-05 00:02:00 |
Explanation:
In the above example, we are adding 2 minutes to the specified date and time.
Example 10:
SELECT datetime('now', '-2 years'); |
Output:
2017-08-05 12:00:07 |
Explanation:
In the above example, we are subtracting 2 years from the current date and time.