SQLite Date() Function

  Total Views : 21
  Comments : 0
 
  Keywords : SQlite date() function query with example, SQLite query to get only date from given datetime string, SQLite date() function to get date in yyyy-mm-dd format with example

Here we will learn SQLite date() function with examples and how to use SQLite date() function to get only date from given datetime string or last day of current month, last date of current month, friendship day of current year with examples 

SQLite date() Function

In SQLite date() function is used to translate given datetime string values into date.

Syntax of SQLite date() Function

Following is the syntax of using SQLite date() function to convert datetime string value into date.

 

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

The SQLite date() function takes datetimestring and one or more modifier values and returns date in a YYYY-MM-DD format. It is important to note that this function will return TEXT as an output.

 

In above SQLite date() syntax the 1st argument 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 fractional part

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

SQLite date() Function Examples

We will see how to use SQLite date() function to get only date from given string with different examples.

 

Following is the example of using SQLite date() function with select statement to get current date.

 

sqlite> SELECT date('now');

 

date('now')

-----------

2016-08-30

Following is the example of using SQLite date() function to get only date from date time string.

 

sqlite> SELECT date('2016-08-30 12:54:12') as 'DATE()';

 

DATE()

----------

2016-08-30

Following is the simple SQLite statement to display last day of current month using date() function.

 

SELECT date('now','start of month','+1 month','-1 day') as 'Last Date of the Month';

Following is the SQLite query to find last day of year using date() function.

 

sqlite> SELECT date('now','start of year','+12 month','-1 day') as 'Last Date of the Year';

 

Last Date of the Year

---------------------

2016-12-31

Following is the example of sqlite query to get last date of current month for next year.

 

sqlite> SELECT date('now','start of month','+13 month','-1 day') as 'Last Date of the Month next year';

 

Last Date of the Month next year

--------------------------------

2017-08-31

Following is the example of SQLite date() function to find date after 4 month from the current date.

 

sqlite> SELECT date('now','+4 month');

 

date('now','+4 month')

----------------------

2016-12-31

Following is the query to get the date of Friendship Day for current year using SQLite date() function.

 

sqlite> SELECT date('now''start of year''+7 month''weekday 0') as 'Friendship day';

 

Friendship day

--------------

2016-08-07

As you know, Friendship day is on first Sunday of August month. So we take modifiers “start of year” and August is 8th month of year so we take another modifier “+7 month” and Sunday is the first day of week so we take modifier 0.

 

This is how we can use SQLite Date() function to get dates based on our requirements.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User