Here we will learn SQLite strftime() function with examples and how to use sqlite strftime() function to format given datetime string to get AM / PM values and calculate the age of a person and finding year, month, date with examples.
In SQLite strftime() function is used to convert given datetime string into a defined format.
Following is the syntax of using strftime() function to convert given datetime string into defined format.
strftime(format, datetimestring, [modifier1,modifier2…,modifier])
The SQLite strftime() function returns a formatted string by taking a datetimestring and formatting it according to format value. If any parameter passed incorrect then it will return a NULL value.
Here, the first parameter of strftime function i.e. format string can contain any of the following markers.
Marker | Description |
---|---|
%d | Day of the month, 01-31 |
%f | seconds with fractional part, SS.SSS |
%H | hour, 00-23 |
%j | day of the year, 001-366 |
%J | Julian Day number |
%m | month, 01-12 |
%M | minute, 00-59 |
%s | seconds since 1970-01-01 |
%S | seconds, 00-59 |
%w | day of the week, starting with Sunday as 0 |
%W | week of the year, 00-53 |
%Y | Year |
%% | a literal % |
The 2nd parameter 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 a fractional part |
The 3rd 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 SQLite strftime() function to format a given datetime string with multiple examples.
If you want to extract Year, Month, and Day for the current date, the following sqlite query can be used.
sqlite> SELECT strftime('%Y/%m/%d','now') as ptime;
ptime
--------------------------
2016/08/31
Here in above example we are extracting year, month and day part from the current date in text format.
If you want to get current time in HH:MM format following SQLite query will return only current time in the required format.
sqlite> SELECT strftime('%H:%M','now') as ptime;
ptime
------------
09:59
Now we will see the example of finding age. Consider birthdate as 28th December 1992.
sqlite> SELECT strftime('%Y','now')- strftime('%Y','1992-12-28') as Age;
Age
----------
24
Now we will see the example of strftime() function with table employee. Consider the employee table is having following records.
eid ename joining_date salary Appraisal date
---------- ---------- ------------ ---------- --------------
1 Rati 2016-08-01 5200.0 2017-02-01
2 Umang 2016-01-01 5700.0 2016-07-01
3 Prachi 2015-06-01 5500.0 2015-12-01
4 Ashmite 2015-11-30 7000.0 2016-05-30
If we want to find out the year, month, and day value of joining_date for those employees who joined in 1 quarter from the table Employee, the following sqlite query can be used.
SELECT eid,ename,joining_date,strftime('%Y', joining_date) as "Year",
strftime('%m', joining_date) as "Month",
strftime('%d', joining_date) as "Day"
FROM Employee
WHERE strftime('%m', joining_date)
IN('01','02','03');
eid ename joining_date Year Month Day
---------- ---------- ------------ ---------- ---------- ----------
2 Umang 2016-01-01 2016 01 01
This is how we can use SQLite strftime() function in queries to format given datetime string values based on our requirements.