DATE Function


 

SQLite: Date Function

This SQLite tutorial 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/time value.
Syntax:

 

Parameters or Arguments:

timestring

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

 

modifier1, modifier2, … modifier_n
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 to the date
NNN months Number of months added to the date
NNN days Number of days added to the date
NNN hours Number of hours added to the date
NNN minutes Number of minutes added to the date
NNN.NNNN seconds Number of seconds (and fractional seconds) added 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)
localtime Adjusts date to localtime, assuming the timestring was expressed in UTC
utc Adjusts date to utc, assuming the timestring was expressed in localtime

 

SQLite supports five date and time functions as follows:

 

The following is a complete list of valid strftime() substitutions:

substitutions Explanation
%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of 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 week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

 

Notice that all other date and time functions can be expressed in terms of strftime():

Function Equivalent strftime()
date(…) strftime(‘%Y-%m-%d’, …)
time(…) strftime(‘%H:%M:%S’, …)
datetime(…) strftime(‘%Y-%m-%d %H:%M:%S’, …)
julianday(…) strftime(‘%J’, …)

 

Note: The date function returns the result displayed as a ‘YYYY-MM-DD’ format. The datetime function returns the result displayed as a ‘YYYY-MM-DD HH:MM:SS’ format and respectively.

 

Examples:

  • Compute the current date.

    date(‘now’)
    2015-06-28

     

  • Compute the last day of the current month.

    date(‘now’,’start of month’,’+1 month’,’-1 day’)
    2015-06-30

     

  • Compute the date and time given a unix timestamp 1092941466.

    datetime(1092941466, ‘unixepoch’)
    2004-08-19 18:51:06

     

  • Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

    datetime(1092941466, ‘unixepoch’, ‘localtime’)
    2004-08-20 00:21:06

     

  • Compute the current unix timestamp.

    strftime(‘%s’,’now’)
    1435478070

     

  • Compute the number of days since the signing of the US Declaration of Independence.

    julianday(‘now’) – julianday(‘1776-07-04’)
    87286.32893754635

     

  • Compute the number of seconds since a particular moment in 2004:

    strftime(‘%s’,’now’) – strftime(‘%s’,’2004-01-01 02:34:56′)
    362553471

     

  • Compute the date of the first Tuesday in October for the current year.

    date(‘now’,’start of year’,’+9 months’,’weekday 2′)
    2015-10-06

     

  • Compute the time since the unix epoch in seconds (like strftime(‘%s’,’now’) except includes fractional part):

    (julianday(‘now’) – 2440587.5)*86400.0
    1435477790.387991

     

  • The Following SQLite statement returns all the employees who hired on ‘2014-01-01’:

    id name position hire_date
    100 chiru developer 1/1/2014
    104 jaya developer 1/1/2014
    107 anitha research analyst 1/1/2014

     

  • The Following SQLite statement returns all the employees who hired between ‘2013-06-01’ to ‘2014-06-01’:

    id name position hire_date
    100 chiru developer 1/1/2014
    103 nithya developer 6/1/2013
    104 jaya developer 1/1/2014
    107 anitha research analyst 1/1/2014
    108 kannan 6/1/2013