w3resource logo


>SQLite date function

SQLite Date and Time Functions

Secondary Nav

Description

SQLite supports five date and time functions as follows:

Name Syntax
date date(timestring, modifier, modifier, ...)
time time(timestring, modifier, modifier, ...)
datetime datetime(timestring, modifier, modifier, ...)
julianday julianday(timestring, modifier, modifier, ...)
strftime strftime(format, timestring, modifier, modifier, ...)

The above mentioned date and time functions take a time string as an argument and followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. The date and time functions use a subset date and time formats.

Here is the list of timestring format :

Name Description
YYYY-MM-DD Year Month and Date
YYYY-MM-DD HH:MM Year Month Date Hour Minute
YYYY-MM-DD HH:MM:SS Year Month Date Hour Minute Second
YYYY-MM-DD HH:MM:SS.SSS Year Month Date Hour Minute Second miliseconds
YYYY-MM-DDTHH:MM "T" is a literal character separating the date and the time,
YYYY-MM-DDTHH:MM:SS "T" is a literal character separating the date and the time,
YYYY-MM-DDTHH:MM:SS.SSS "T" is a literal character separating the date and the time,
HH:MM specify only a time assume a date of 2000-01-01.
HH:MM:SS specify only a time assume a date of 2000-01-01.
HH:MM:SS.SSS specify only a time assume a date of 2000-01-01.
now current date and time
DDDDDDDDDD A Julian day number expressed as a floating point value.

Here is the list of modifiers:

Name Description
NNN days number of days
NNN hours number of hours
NNN minutes number of minutes
NNN.NNNN seconds numhber of miliseconds
NNN months numher of months
NNN years number of years
start of month The "start of" modifiers shift the date backwards to the beginning of the current month.
start of year The "start of" modifiers shift the date backwards to the beginning of the current year.
start of day The "start of" modifiers shift the date backwards to the beginning of the current day.
weekday N The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.
unixepoch The "unixepoch" modifier only works if it immediately follows a timestring in the DDDDDDDDDD format.
localtime The "localtime" modifier assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime.
utc The "utc" is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC.

Here is the list of format specifiers:

Format Description
%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
%% %


Join our Question Answer community to learn and share your programming knowledge.

Help the community:

Python: Fizzbuzz

C++: Decimal to binary conversion

JavaScript: Need Help in JavaScript

Python: Help me with this program