SQLite Date Function

The SQLite “DATE” function is used to retrieve the date and return it in ‘YYYY-MM-DD’ format.

Syntax:

Here, timestring is a date value which can be anyone of the following:

Index	timestring	Description
1)	now	It is a literal used to return the current date.
2)	YYYY-MM-DD	It specifies the date value formatted as ‘YYYY-MM-DD’
3)	YYYY-MM-DD HH:MM	It specifies the date value formatted as ‘YYYY-MM-DD HH:MM’
4)	YYYY-MM-DD HH:MM:SS	It specifies the date value formatted as ‘YYYY-MM-DD HH:MM:SS’
5)	YYYY-MM-DD HH:MM:SS.SSS	It specifies the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’
6)	HH:MM	It specifies the date value formatted as ‘HH:MM’
7)	HH:MM:SS	It specifies the date value formatted as ‘HH:MM:SS’
8)	HH:MM:SS.SSS	It specifies the date value formatted as ‘HH:MM:SS.SSS’
9)	YYYY-MM-DDTHH:MM	It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM’ where t is a literal character separating the date and time portions.
10)	YYYY-MM-DDTHH:MM:SS	It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’ where t is a literal character separating the date and time portions
11)	YYYY-MM-DDTHH:MM:SS.SSS	It specifies the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’ where t is a literal character separating the date and time portions
12)	DDDDDDDDDD	It specifies the Julian date number

modifier1, modifier2, … modifier_n: modifiers are optional. These are used with timestring to add or subtract time, date or years.

Index	Modifier	Description
1)	[+-]NNN years	It is used to specify number of years added/subtracted to the date
2)	[+-]NNN months	It is used to specify number of months added/subtracted to the date
3)	[+-]NNN days	It is used to specify number of days added/subtracted to the date
4)	[+-]NNN hours	It is used to specify number of hours added/subtracted to the date
5)	[+-]NNN minutes	It is used to specify number of minutes added/subtracted to the date
6)	[+-]NNN seconds	It is used to specify number of seconds added/subtracted to the date
7)	[+-]NNN.NNNN seconds	It is used to specify number of seconds (and fractional seconds) added/subtracted to the date
8)	start of year	It is used to shift the date back to the start of the year
9)	start of month	It is used to shift the date back to the start of the month
10)	start of day	It is used to shift the date back to the start of the day
11)	weekday N	It is used to move 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)
12)	unixepoch	It is used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
13)	localtime	It is used to adjust date to localtime, assuming the timestring was expressed in UTC
14)	utc	It is used to adjust date to utc, assuming the timestring was expressed in localtime

Example1:

Retrieve current date:

Output:

SQLite Date time function 1

Example2: Retrieve First Day of the Month:

There are 4 ways to find out first day of the month:

 SELECT date(‘2017-12-17’, ‘start of month’); SELECT date(‘now’, ‘start of month’); SELECT date(‘2014-10-16’, ‘-15 days’);  SELECT date(‘now’, ‘-11 days’); 

Output:

SQLite Date time function 2 SQLite Date time function 3 SQLite Date time function 4 SQLite Date time function 5

Example3: Retrieve last day of the month:

Date function can be used to retrieve the last day of the month. There are 4 ways to find out last day of the month:

 SELECT date(‘2017-04-13’, ‘start of month’,’+1 month’, ‘-1 day’);  SELECT date(‘now’, ‘start of month’,’+1 month’, ‘-1 day’);  SELECT date(‘2017-04-13’, ‘+17 days’);  SELECT date(‘now’, ‘+17 days’);  

Output:

SQLite Date time function 6 SQLite Date time function 7 SQLite Date time function 8 SQLite Date time function 9

Example4: Add/ subtract years to Current date:

Add and subtract 5 years to current date:

 SELECT date(‘now’,’+5 years’);  SELECT date(‘2017-04-13′,’+5 years’);  SELECT date(‘now’,’-5 years’);  SELECT date(‘2017-04-13′,’-5 years’);  

Output:

SQLite Date time function 10 SQLite Date time function 11 SQLite Date time function 12 SQLite Date time function 13

Example5: Add/ subtract days to Current date:

By the same above way you can add and subtract days to the date:

 SELECT date(‘now’,’+5 days’);  SELECT date(‘2017-04-13′,’+5 days’);  SELECT date(‘now’,’-5 days’);  SELECT date(‘2017-04-13′,’-5 days’);  

Output:

SQLite Date time function 14 SQLite Date time function 15 SQLite Date time function 16 SQLite Date time function 17

Next TopicSQLite DateTime

SQLite date

SQLite Date Function

SUM Function in SQL

Event Listener

You may also like