w3resource

SQLite date() function

Description

The date() function returns the date in this format: YYYY-MM-DD.

Syntax:

date(timestring, modifier, modifier, ...)

Example-1:

If you want to know the current date, the following SQL can be used.

SELECT date('now');

Here is the result.

date('now')
-----------
2014-10-31

Example-2:

If you want to display a date time in date format, the following SQL can be used.

SELECT date('2014-10-07 11:31:31') as Date;

Here is the result.

Date
----------
2014-10-07

Example-3:

If you want to display the last day of the current month, the following SQL can be used.

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

Here is the result.

Last Date of the Month
----------------------
2014-10-31

Example-4:

If you want to get the last day of a year, the following SQL statements can be used.

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

Here is the result.

Last Date of the Year
---------------------
2014-12-31

Example-5:

If you want to get the last date of the current month for next year, the following SQL statements can be used.

SELECT date('now','start of month','+13 month','-1 day') as "Last Date of current month after a Year";

Here is the result.

Last Date of current month after a Year
---------------------------------------
2015-10-31

Example-6:

If you want to get the last date of the current month after 4 years, the following SQL statements can be used.

SELECT date('now','start of month','+49 month','-1 day') as "Last Date of current after four Years";

Here is the result.

Last Date of current after four Years
-------------------------------------
2018-10-31

Example-7:

If you want to get the date of first Friday of the august for the current year, the following SQL statements can be used.

SELECT date('now','start of year','+7 month','weekday 6') as "Last Date of current after four Years";

Here is the result.

First Friday of August
----------------------
2014-08-01

Example-8:

If you want to get the date after two months from the current date, the following SQL statements can be used.

SELECT date('now','+2 month') as "Date after Tow Months";

Here is the result.

Date after Tow Months
---------------------
2014-12-31

Previous: ntroduction
Next: TIME