To make Teradata SQL more ANSI compliant and compatible with other RDBMS SQL, NCR has added INTERVAL processing. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.
Although Teradata allowed arithmetic on DATE and TIME, it was not performed in accordance to ANSI standards and therefore, an extension instead of a standard. With INTERVAL being a standard instead of an extension, more SQL can be ported directly from an ANSI compliant database to Teradata without conversion.
Additionally, when a data value was used to perform date or time math, it was always "assumed" to be at the lowest level for the definition (days for DATE and seconds for TIME). Now, any portion of either can be expressed and used.
To use the ANSI syntax for intervals, the SQL statement must be very specific as to what the data values mean and the format in which they are coded. ANSI standards tend to be lengthier to write and more restrictive as to what is and what is not allowed regarding the values and their use.
Simple INTERVAL Examples using literals:
Complex INTERVAL Examples using literals:
For several of the INTERVAL literals, their use seems obvious based on the literal nonnumeric literals used. However, notice that the HOUR TO MINUTE and the MINUTE TO SECOND above, are not so obvious. Therefore, the declaration of the meaning is important. For instance, notice that they are coded as character literals. This allows for use of a slash (/), colon (: ) and space as part of the literal. Also, notice the use of a negative time frame requires a "−" sign to be outside of the quotes. The presence of the quotes also denotes that the numeric values are treated as character for conversion to a point in time.
The format of a timestamp requires the space between the day and hour when using intervals. For example, notice the blank space between the day and hour in the compound DAY TO HOUR interval. Without the space, it is an error.
INTERVAL Arithmetic with DATE and TIME
To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations.
Note: It makes little sense to add two dates together.
Traditionally, the output of the subtraction is an integer, up to 2.147 billion. However, Teradata knows that when an integer is used in a formula with a date, it must represent a number of days. The following uses the ANSI representation for a DATE:SELECT (DATE '1999-10-01' - DATE '1988-10-01') AS Assumed_Days ;
The next SELECT uses the ANSI explicit DAY interval:SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY AS Actual_Days ; **** Failure 7453 Internal Field Overflow
The above request fails on an overflow of the INTERVAL. Using this ANSI interval, the output of the subtraction is an interval with 4 digits. The default for all intervals is 2 digits and therefore the overflow occurs until the SELECT is modified with DAY(4), below:SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY(4) AS Actual_Days ; 1 Row Returned
Normally, a date minus a date yields the number of days between them. To see months instead, the following SELECT operations use literals to demonstrate the conversions performed on various DATE and INTERVAL data:SELECT (DATE '2000-10-01' – DATE '1999-10-01') MONTH (Title 'Months') ; 1 Row Returned
The next SELECT shows INTERVAL operations used with TIME:
Although Intervals tend to be more accurate, they are more restrictive and therefore, more care is required when coding them into the SQL constructs. However, one miscalculation, like in the overflow example, and the SQL fails. Additionally, 9999 is the largest value for any interval. Therefore, it might be required to use a combination of intervals, such as: MONTHS to DAYS in order to receive an answer without an overflow occurring.
CAST Using Intervals
The CAST function was seen in an earlier chapter as the ANSI method for converting data from one type to another. It can also be used to convert one INTERVAL to another INTERVAL representation. Although the CAST is normally used in the SELECT list, it works in the WHERE clause for comparison reasons.
Below is the syntax for using the CAST with a date:
The following converts an INTERVAL of 6 years and 2 months to an INTERVAL number of months:SELECT CAST( (INTERVAL '6-02' YEAR TO MONTH) AS INTERVAL MONTH );
Logic seems to dictate that if months can be shown, the years and months should also be available. This request attempts to convert 1300 months to show the number of years and months:
The above failed because the number of months takes more than two digits to hold a number of years greater than 99. The fix is to change the YEAR to YEAR(3) and rerun:SELECT CAST((INTERVAL '1202' MONTH) AS INTERVAL YEAR(3) TO MONTH ) (Title 'Years & Months') ;
Years & Months
The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible with Teradata.
At the same time, care must be taken to use a representation that is large enough to contain the answer. The default is 2 digits and anything larger, 4 digits maximum, must be literally requested. The incorrect size results in an SQL runtime error.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.