17.00 - 17.05 - Definition of Normalization - 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)

The normalization process is explained more formally as follows.

When the DAY field of the source date_expression or timestamp_expression is greater than the resulting target DAY field, ADD_MONTHS sets DD equal to the last day of the month + n to normalize the reported date or timestamp.

Define date_expression as ‘YYYY-MM-DD’ for simplicity.

For a given date_expression, you can then express the syntax of ADD_MONTHS as follows.

ADD_MONTHS('YYYY-MM-DD' , n)

Recalling that n can be negative, and substituting ‘YYYY-MM-DD’ for date_expression, you can redefine ADD_MONTHS in terms of ANSI SQL:2011 dates and intervals as follows.

ADD_MONTHS('YYYY-MM-DD', n) = 'YYYY-MM-DD' ± INTERVAL 'n' MONTH

The equation is true unless a non valid date such as 1999-09-31 results, in which case the ANSI expression traps the non valid date exception and returns an error.

ADD_MONTHS, on the other hand, processes the exception and returns a valid, though not necessarily expected, date. The algorithm ADD_MONTHS uses to produce its normalized result is as follows, expressed as pseudocode.

WHEN
DD > last_day_of_the_month(MM+n)
THEN SET
DD = last_day_of_the_month(MM+n)

This property is also true for the date portion of any timestamp_expression.

Note that normalization produces valid results for leap years.