17.10 - Examples of Queries of Calendar Data - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Dictionary

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1092-171K
Language
English (United States)

These examples show simple queries that you can use to determine the day of the week (day_of_week) and the date at the beginning of the week (weekBegin). The session calendars are the ISO and COMPATIBLE system-defined calendars.

  • The first two examples provide queries that return the day of the week (for example, day 1, 2, or 7) for a particular date.
  • The last two examples provide queries that return the date of the first day of a week (day 1) for the week to which a particular date belongs.

Example: Day of the Week (ISO Calendar)

Sel day_of_week from Sys_Calendar.Calendar where calendar_date = date '2011-01-01';
day_of_week
-----------
6

Example: Day of the Week (COMPATIBLE Calendar)

Sel day_of_week from Sys_Calendar.Calendar where calendar_date = date '2011-01-01';
day_of_week
-----------
1

Example: Beginning of the Week (ISO Calendar)

Sel weekBegin from Sys_Calendar.BusinessCalendar where calendar_date = date '2011-01-01';
WeekBegin
-----------
10/12/27

Example: Beginning of the Week (COMPATIBLE Calendar)

Sel weekBegin from Sys_Calendar.BusinessCalendar where calendar_date = date '2011-01-01';
WeekBegin
-----------
11/01/01