17.00 - 17.05 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

Example: Querying for the First Tuesday After a Specified Date

The following query:

SELECT NEXT_DAY(DATE '2009-12-20', 'TUESDAY'); 

returns the result 09/12/22 since 12/22 is the next Tuesday after Sunday, Dec 20, 2009.

Example: Querying for the First Friday After a Specified Date

The following query:

SELECT NEXT_DAY(DATE '2009-12-20', 'FRIDAY');

returns the result 09/12/25 since 12/25 is the next Friday after Sunday, Dec 20, 2009.

Example: Querying for the First Friday After a Different Specified Date

The following query:

SELECT NEXT_DAY(DATE '2009-12-25', 'FRIDAY'); 

returns the result 10/01/01 since Jan 1, 2010 is the next Friday after Friday, Dec 25, 2009.

Example: Non-English day_values

NEXT_DAY (date_timestamp_value, day_value) works with the Specification for Data Formatting. This is an example of the Japanese tdlocaledef.txt and how NEXT_DAY works with those definitions.

// DBS System Formatting Data
// Day and month names
ShortDays {
"\u65e5";
"\u6708";
"\u706b";
"\u6c34";
"\u6728";
"\u91d1";
"\u571f"
}
LongDays {
"\u65e5\u66dc\u65e5";
"\u6708\u66dc\u65e5";
"\u706b\u66dc\u65e5";    /*火曜日*/
"\u6c34\u66dc\u65e5";
"\u6728\u66dc\u65e5";
"\u91d1\u66dc\u65e5";
"\u571f\u66dc\u65e5"
}
ShortMonths {
"1\u6708";
"2\u6708";
"3\u6708";
"4\u6708";
"5\u6708";
"6\u6708";
"7\u6708";
"8\u6708";
"9\u6708";
"10\u6708";
"11\u6708";
"12\u6708"
}

The following SQL fails with an error:

select next_day(to_date('2015/06/22','YYYY/MM/DD'),'Tue')  AS "日付";

Instead, use the Japanese day_value defined in the tdlocaledef.txt as follows:

select next_day(to_date('2015/06/22','YYYY/MM/DD'),'火曜日')  AS "日付" ;
日付
---------
15/06/23
"\u706b\u66dc\u65e5"; indicates "Tuesday" in Japanese as demonstrated below.
Sel _UNICODE U&'#706b#66dc#65e5' UESCAPE '#';
' 火曜日'
-----------------
 火曜日