SQLite Strftime() Function

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.

SQLite strftime() Function

In SQLite strftime() function is used to convert given datetime string into a defined format.

Syntax of SQLite strftime() Function

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.

 

MarkerDescription
%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.

 

FormatExample
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

 

FormatExample
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

Examples of SQLite strftime() Function

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. 

Previous
 
Next