Sqlite Date Function
SQLite: date Function
This SQLite post explains how to use the SQLite date function with syntax and examples.
Description
The SQLite date function is a very powerful function that can calculate a date and return it in the format 'YYYY-MM-DD'.
Syntax
The syntax for the date function in SQLite is:
date(timestring, [ modifier1, modifier2, ... modifier_n ] )Parameters or Arguments
- timestring
- modifier1, modifier2, ... modifier_n
A date value. It can be one of the following:
| timestring | Explanation |
|---|---|
| now | now is a literal used to return the current date. |
| 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' |
| 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' |
| YYYY-MM-DDTHH:MM | Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions |
| YYYY-MM-DDTHH:MM:SS | Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions |
| 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 time portions |
| DDDDDDDDDD | Julian date number |
Optional. These are modifiers that are applied to the timestring. Each modifier is applied in order and are cumulative. They can be one or more of the following:
| modifier | Explanation |
|---|---|
| [+-]NNN years | Number of years added/subtracted to the date |
| [+-]NNN months | Number of months added/subtracted to the date |
| [+-]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 seconds | Number of seconds added/subtracted to the date |
| [+-]NNN.NNNN seconds | Number of seconds (and fractional seconds) added/subtracted to the date |
| start of year | Shifting the date back to the start of the year |
| start of month | Shifting the date back to the start of the month |
| 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 timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01) |
| localtime | Adjusts date to localtime, assuming the timestring was expressed in UTC |
| utc | Adjusts date to utc, assuming the timestring was expressed in localtime |
Note
- The date function returns the result displayed as a 'YYYY-MM-DD' format.
Applies To
The date function can be used in the following versions of SQLite:
- SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x
Example
Let's look at some SQLite date function examples and explore how to use the date function in SQLite.
Current Date Example
You could retrieve the current date in SQLite using the "now" timestring with the date function as follows:
sqlite> SELECT date('now');
Output: '2014-10-16' (formatted as YYYY-MM-DD)First Day of the Month Example
You could retrieve the first day of the month using the date function as follows:
sqlite> SELECT date('2014-10-16', 'start of month');
Output: '2014-10-01'
sqlite> SELECT date('now', 'start of month');
Output: '2014-10-01' (assuming current date is 2014-10-16)
sqlite> SELECT date('2014-10-16', '-15 days');
Output: '2014-10-01'
sqlite> SELECT date('now', '-15 days');
Output: '2014-10-01' (assuming current date is 2014-10-16)In these examples, we've calculated the first day of the month in a few different ways. The 'start of month' modifier lets us take any date value and calculate the first day of the month for that date. This includes using the 'now' timestring.
We can also add or subtract days to a date value using the 'NNN day' modifier. In our examples, we subtracted 15 days from our date values. Since we had a date value of the 2014-10-16, this returns the first day of the month (ie: 2014-10-01).
Last Day of the Month Example
You could retrieve the last day of the month using the date function as follows:
sqlite> SELECT date('2014-10-16', 'start of month','+1 month', '-1 day');
Output: '2014-10-31'
sqlite> SELECT date('now', 'start of month','+1 month', '-1 day');
Output: '2014-10-31' (assuming current date is 2014-10-16)
sqlite> SELECT date('2014-10-16', '+15 days');
Output: '2014-10-31'
sqlite> SELECT date('now', '+15 days');
Output: '2014-10-31' (assuming current date is 2014-10-16)In these examples, we've calculated the last day of the month in a few different ways. First, we use the 'start of month' modifier to calculate the first day of the month and then add 1 month and then subtract 1 day.
We can also add or subtract days to a date value using the 'NNN day' modifier. In our examples, we added 15 days to our date values. Since we had a date value of the 2014-10-16, this returns the last day of the month (ie: 2014-10-31).
Adding/Subtracting Years Example
You can use the date function to manipulate a date value and add or subtract years to it. This is done using the 'NNN years' modifier with the date function as follows:
sqlite> SELECT date('2014-10-16','+2 years');
Output: '2016-10-16'
sqlite> SELECT date('2012-10-16','-2 years');
Output: '2010-10-14'
sqlite> SELECT date('now','+5 years');
Output: '2019-10-16' (assuming current date is 2014-10-16)In these examples, we've used the 'NNN years' modifier to add 2 years in the first example, subtract 2 years in the second example, and add 5 years to the current date in the third example.
Adding/Subtracting Days Example
You can use the date function to manipulate a date value and add or subtract days to it. This is done using the 'NNN days' modifier with the date function as follows:
sqlite> SELECT date('2014-10-16','+7 days');
Output: '2014-10-23'
sqlite> SELECT date('2014-10-16','-7 days');
Output: '2014-10-09'
sqlite> SELECT date('now','+10 days');
Output: '2014-10-06' (assuming current date is 2014-10-16)In these examples, we've used the 'NNN days' modifier to add 7 days in the first example, subtract 7 days in the second example, and add 10 days to the current date in the third example.
