DATE/TIME FUNCTIONS in Teradata

Share via:

Dear Readers,

Let’s understand what the date/time functions in teradata are and different operations associated with them.

How is the date stored in Teradata ?

Dates are stored as integer internally using the following formula.

((YEAR – 1900) * 10000) + (MONTH * 100) + DAY

So, the date 2020-07-16 will store internally as below :

Below query can also be used to perform the above calculation :

SELECT CAST(CURRENT_DATE AS INTEGER);

Display of DATEFORM in BTEQ

There are two types of form to display the date in BTEQ. These can be set using DATEFORM.

  1. INTEGERDATE and
  2. ANSIDATE

The default is INTEGERDATE.

  1. INTEGERDATE format : YY/MM/DD/*Teradata date format*/

Syntax :

Example :

BTEQ — Enter your SQL request or BTEQ command:

  1. ANSIDATE format : YYYY-MM-DD

Syntax :

Example :

Since the dates are stored as integer, you can perform a variety of arithmetic operations on them. Teradata provides functions to perform these operations.

Let us see few of them as mentioned below :

FUNCTIONS DESCRIPTION
NEXT_DAY Returns the date that follows a given date
LAST_DAY Returns last day of the given month. It may contain timestamp values also
MONTHS_BETWEEN Returns number of months between two date (timestamp) values. The result is always an integer value.
ADD_MONTHS Adds a month to the given date (timestamp) value and returns resulting date value
TO_DATE Converts a string value to a DATE value and returns resulting date value
TO_TIMESTAMP Convert a string value to a TIMESTAMP value and return resulting timestamp value
ROUND Returns a DATE value with the time portion rounded to the unit specified by a format string
TRUNC Returns a DATE value with the time portion truncated to the unit specified by a format string
EXTRACT Function extracts portions of day, month and year from a given date value
INTERVAL Used to perform arithmetic operations on DATE and TIME values
FORMAT Change the display of the date in the specified format
Let us understand them in little detail with examples.
EXTRACT
  • This function extracts portions of day, month and year from a DATE value.
  • This function is also used to extract hour, minute and second from TIME/TIMESTAMP value.
Example:
  1. Extract year from date

  1. Extract month from date

  1. Extract day from date

  1. Extract hour from timestamp

  1. Extract minute from timestamp

  1. Extract second from timestamp

INTERVAL

Year-Month Interval

Day-Time Interval

Example :

Add 5 years to current date.

Add 7 years and 03 month to current date.

  1. Add 04 days, 10 hours and 5 minutes to current timestamp.

SELECT 10000* Interval ‘1’ SECOND;

Interval fields can hold upto 4 digits only

NEXT_DAY

Returns the next coming date whose day of the week is equivalent to mentioned day of week.

Given date is 2020/07/16 and the Monday after this date is 2020/07/20.

LAST_DAY

Returns the last day of the month against the given date.

Last day of the given date is 31st July.

ROUND

Returns the rounded date based on character_code passed.

Syntax:

Example :

26th July is the start of the next week. 24th July is Friday and 26th July is Sunday.

19th July is the start day of the 22nd July week. 22nd July is Wednesday and 19th July is Sunday.

TRUNC

Returns the truncated date based on character_code passed.

Syntax: TRUNC(date_value,[character_code])

Example :

FORMAT

Date/Timestamps can be formatted using ‘FORMAT clause.

Example :

Here we have displayed AM/PM using ‘t’ & ‘B’ is used for space.

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment