Here we will learn SQLite date() function with examples and how to use SQLite date() function to get the only date from given datetime string or last day of the current month, last date of the current month, friendship day of the current year with examples
In SQLite date() function is used to translate given datetime string values into date.
Following is the syntax of using SQLite date() function to convert datetime string value into date.
date(datetimestring, [modifier1, modifier2…, modifierN])
The SQLite date() function takes datetimestring and one or more modifier values and returns date in a YYYY-MM-DD format. It is important to note that this function will return TEXT as an output.
In above SQLite date() syntax the 1st argument datetimestring will accept the values in following formats.
| Format | Example |
|---|---|
| now | 2016-08-30 |
| YYYY-MM-DD | 2016-08-30 |
| YYYY-MM-DD HH:MM | 2016-08-30 18:47 |
| YYYY-MM-DD HH:MM:SS | 2016-08-30 18:47:56 |
| YYYY-MM-DD HH:MM:SS.sss | 2016-08-30 18:47:56.235 |
| YYYY-MM-DDTHH:MM | T literal is used to separate date & time |
| YYYY-MM-DDTHH:MM:SS | T literal is used to separate date & time |
| YYYY-MM-DDTHH:MM:SS.sss | T literal is used to separate date & time |
| HH:MM | 18:50 |
| HH:MM:SS | 06:52:25 |
| HH:MM:SS.sss | 06:26:56.123 |
| DDDDDDD | Julian date number |
| DDDDDDD.ddddddd | Julian date number with fractional part |
The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in the following format
| Format | Example |
|---|---|
| XX days | 02 days |
| XX hours | 10 hours |
| XX minutes | 15 minutes |
| XX.XXXX seconds | 10.15 seconds |
| XX months | 04 months |
| XX years | 02 years |
| start of month | start of month |
| start of year | start of year |
| start of day | start of day |
| weekday X | weekday 2 |
| unixepoch | unixepoch |
| localtime | localtime |
| utc | utc |
We will see how to use the SQLite date() function to get only date from the given string with different examples.
Following is the example of using SQLite date() function with select statement to get current date.
sqlite> SELECT date('now');
date('now')
-----------
2016-08-30
Following is the example of using the SQLite date() function to get the only date from date time string.
sqlite> SELECT date('2016-08-30 12:54:12') as 'DATE()';
DATE()
----------
2016-08-30
Following is the simple SQLite statement to display the last day of the current month using date() function.
SELECT date('now','start of month','+1 month','-1 day') as 'Last Date of the Month';
Following is the SQLite query to find the last day of the year using date() function.
sqlite> SELECT date('now','start of year','+12 month','-1 day') as 'Last Date of the Year';
Last Date of the Year
---------------------
2016-12-31
Following is the example of sqlite query to get last date of current month for next year.
sqlite> SELECT date('now','start of month','+13 month','-1 day') as 'Last Date of the Month next year';
Last Date of the Month next year
--------------------------------
2017-08-31
Following is the example of the SQLite date() function to find date after 4 month from the current date.
sqlite> SELECT date('now','+4 month');
date('now','+4 month')
----------------------
2016-12-31
Following is the query to get the date of Friendship Day for current year using SQLite date() function.
sqlite> SELECT date('now', 'start of year', '+7 month', 'weekday 0') as 'Friendship day';
Friendship day
--------------
2016-08-07
As you know, Friendship day is on first Sunday of August month. So we take modifiers “start of year” and August is 8th month of the year so we take another modifier “+7 month” and Sunday is the first day of week so we take modifier 0.
This is how we can use SQLite Date() function to get dates based on our requirements.