SQLite Date and Time Functions

Here we will learn sqlite date and time functions with examples and different type of date & time functions (date, time, datetime, julianday, strftime) and change the format of date & times (dd/mm/yyyy, YYYY-MM-DD, etc) with examples.

SQLite Date & Time

In SQLite, we don't have any storage class to store Date and time values because of SQLite offers some date functions and time literals that can store date & time as TEXT, REAL, or INTEGER.


Following are the different types of Date and Time functions available in SQLite.



Generally, the SQLite Date & Time functions syntaxes will be like as shown following to get only date or time or datetime, etc. based on our requirements.


date(datetimestring, [modifier1, modifier2…, modifierN])




time(datetimestring, [modifier1, modifier2…, modifierN])




datetime(datetimestring, [modifier1, modifier2…, modifierN])

Here the 1st argument datetimestring will accept the values in the following formats.


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 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in the following format


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 learn all these date and time functions in detailed manner with examples in next chapters.