This page has not been translated into your language. Request translation

NEXT_DAY function

PDFRSS

NEXT_DAY returns the date of the first instance of the specified day that is later than the given date.

If the day value is the same day of the week as the given date, the next occurrence of that day is returned.

Syntax

NEXT_DAY( { date | timestamp }, day )

Arguments

date | timestamp

A column of data type DATE or TIMESTAMP or an expression that implicitly evaluates to a DATE or TIMESTAMP type.

day

A string containing the name of any day. Capitalization doesn't matter.

Valid values are as follows.

Day	Values
Sunday	Su, Sun, Sunday
Monday	M, Mo, Mon, Monday
Tuesday	Tu, Tue, Tues, Tuesday
Wednesday	W, We, Wed, Wednesday
Thursday	Th, Thu, Thurs, Thursday
Friday	F, Fr, Fri, Friday
Saturday	Sa, Sat, Saturday

Return type

DATE

Examples

The following example returns the date of the first Tuesday after 8/20/2014.

select next_day('2014-08-20','Tuesday');

next_day
-----------
2014-08-26

The following example returns the date of the first Tuesday after 1/1/2008 at 5:54:44.

select listtime, next_day(listtime, 'Tue') from listing limit 1;

listtime            | next_day
--------------------+-----------
2008-01-01 05:54:44 | 2008-01-08

The following example gets target marketing dates for the third quarter.

select username, (firstname ||' '|| lastname) as name,
eventname, caldate, next_day (caldate, 'Monday') as marketing_target
from sales, date, users, event
where sales.buyerid = users.userid
and sales.eventid = event.eventid
and event.dateid = date.dateid
and date.qtr = 3
order by marketing_target, eventname, name;

username  |     name          |     eventname        |    caldate   |   marketing_target
----------+-------------------+----------------------+--------------+-------------------
MBO26QSG  |   Callum Atkinson | .38 Special          |  2008-07-06  |	2008-07-07
WCR50YIU  |   Erasmus Alvarez | A Doll's House       |  2008-07-03  |	2008-07-07
CKT70OIE  |   Hadassah Adkins | Ana Gabriel          |  2008-07-06  |	2008-07-07
VVG07OUO  |   Nathan Abbott   | Armando Manzanero    |  2008-07-04  |	2008-07-07
GEW77SII  |   Scarlet Avila   | August: Osage County |  2008-07-06  |	2008-07-07
ECR71CVS  |   Caryn Adkins    | Ben Folds            |  2008-07-03  |	2008-07-07
KUW82CYU  |   Kaden Aguilar   | Bette Midler         |  2008-07-01  |	2008-07-07
WZE78DJZ  |   Kay Avila       | Bette Midler         |  2008-07-01  |	2008-07-07
HXY04NVE  |   Dante Austin    | Britney Spears       |  2008-07-02  |	2008-07-07
URY81YWF  |   Wilma Anthony   | Britney Spears       |  2008-07-02  |	2008-07-07