CodesJava

Easy learning with example program codes

Date() Function in SQLite

SQLite Date Function
To get a date value, the SQLite date function is used. It returns a date value in ‘YYYY-MM-DD’ format.

Syntax:

date( time_string, [ modifier1, modifier2, ... modifier_n ] )   

Time_string:

TIMESTRINGUSES
nowTo get the current date.
YYYY-MM-DDTo specify the date value formatted as ‘YYYY-MM-DD’.
YYYY-MM-DD HH:MMTo specify the date value formatted as ‘YYYY-MM-DD HH:MM’.
YYYY-MM-DD HH:MM:SSTo specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS’.
YYYY-MM-DD HH:MM:SS.SSSTo specify the date value formatted as ‘YYYY-MM-DD HH:MM:SS.SSS’.
HH:MMTo specify the date value formatted as ‘HH:MM’.
HH:MM:SSTo specify the date value formatted as ‘HH:MM:SS’.
HH:MM:SS.SSSTo specify the date value formatted as ‘HH:MM:SS.SSS’.
YYYY-MM-DDTHH:MMTo specify the date value formatted as ‘YYYY-MM-DDTHH:MM’. Here, T is a literal character used to separate the date and time portions.
YYYY-MM-DDTHH:MM:SSTo specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS’. Here, T is a literal character used to separate the date and time portions.
YYYY-MM-DDTHH:MM:SS.SSSTo specify the date value formatted as ‘YYYY-MM-DDTHH:MM:SS.SSS’. Here, T is a literal character used to separate the date and time portions.
DDDDDDDDDDTo specify the Julian date number.

 

Modifier:
Modifiers are used to add or subtract time, date or years.

MODIFIERUSES
[+-]NNN yearsSpecifies the number of years added/subtracted to the date.
[+-]NNN monthsSpecifies the number of months added/subtracted to the date.
[+-]NNN daysSpecifies the number of days added/subtracted to the date.
[+-]NNN hoursSpecifies the number of hours added/subtracted to the date.
[+-]NNN minutesSpecifies the number of minutes added/subtracted to the date.
[+-]NNN secondsSpecifies the number of seconds added/subtracted to the date.
[+-]NNN.NNNN secondsSpecifies the number of seconds (and fractional seconds) added/subtracted to the date.
start of yearShifts the date back to the start of the year.
start of monthShifts the date back to the start of the month.
start of dayShifts the date back to the start of the day.
weekday NMove 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).
unixepochInterprets the date as UNIX Time (ie: number of seconds since 1970-01-01).
localtimeAdjusts the date to localtime, assuming the time string was expressed in UTC.
utcAdjusts the date to utc, assuming the time string was expressed in localtime.

 
Example1:

SELECT date('now');

Output:

2019-08-05

Explanation:
In the above example, we are retrieving the current date.

Example 2:

SELECT date('now', 'start of month');

Output:

2019-08-01

Explanation:
In the above example, we are retrieving the first day of the month.

Example 3:

SELECT date('2019-08-05', 'start of month');

Output:

2019-08-01

Explanation:
In the above example, we are retrieving the first day of the month.

Example 4:

SELECT date('now', 'start of month','+1 month', '-1 day');

Output:

2019-08-31

Explanation:
In the above example, we are retrieving the last day of the month.

Example 5:

SELECT date('2019-08-05', 'start of month','+1 month', '-1 day');

Output:

2019-08-31

Explanation:
In the above example, we are retrieving the last day of the month.

Example 6:

SELECT date('now','+2 years');

Output:

2021-08-05

Explanation:
In the above example, we are adding 2 years to the current date.

Example 7:

SELECT date('2019-08-05','+2 years');

Output:

2021-08-05

Explanation:
In the above example, we are adding 2 years to the specified date.

Example 8:

SELECT date('now','+2 days');

Output:

2019-08-07

Explanation:
In the above example, we are adding 2 days to the current date.

Example 9:

SELECT date('2019-08-05','+2 days');

Output:

2019-08-07

Explanation:
In the above example, we are adding 2 days to the specified date.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status