In Teradata, there are various date formats & date functions available and I have seen so many people getting confused and committing mistakes while handling columns & values of DATE datatype. Also fixing date related errors sometime is not a straightforward tasks. In this post, we will see different date functions we can use to retrieve required date value as per requirement.
Check Teradata Session Default Date Format
Easiest method to check default date format for the current session is to run "HELP SESSION" command. Now I want you to notice the value for "Current DateForm" & "Default Date Format" columns. It is "IntegerDate" & "YY/MM/DD" in my case. This should be your first step always.
Changing the default Date Format
In Teradata, there are only 2 DateForm available – IntegerDate or ANSIDate. If required in some cases then you can change the DateForm by using the command below:
set session dateform=ANSIDATE; –(or IntegerDate)
Now check the default date format of the session by using "HELP SESSION". If you still don't see the "default date format" as the one you wanted then reach out to your DBA and he can change the default format for date for IntegerDate or ANSIDate.
Once you have verified the default date format for session you can apply and debug different DATE Functions in Teradata easily.
Also remember in Teradata, a CALENDAR refers to DATE RANGE between 01-January-1900 to 31-December-2100. So whenever any function refers to keyword CALENDAR, I want you to consider time range from 1900-2100 only.
You can query sys_calendar.calendar table in Teradata to get more details about each day like week, month, quarter, day etc. or you can directly use newly added functions in Teradata to fetch these values.
Convert STRING to DATE FORMAT
To convert STRING to DATE, the string value should be supplied in the exact format as the DATE FORMAT required in output. If the supplied string is in some other format, then first CAST the STRING to DATE and then apply some other FORMAT of DATE to it.
SELECT CAST('2013-02-12' AS DATE FORMAT 'DD/MM/YY');
The above SQL will result in error. Because the supplied string is not in same format as the output DATE is required. To overcome this problem, try below SQL.
SELECT CAST(CAST('2013-02-12' AS DATE) AS DATE FORMAT 'DD/MM/YY');
Storing DATE as INTEGER Value
Date columns are stored as INTEGER internally by Teradata. To calculate integer value for any DATE column in TERADATA, the manner in which it will be stored in TERADATA, try this:
(YEAR-1900)*10000 + Month * 100 + Day
If DATE is 2013-03-13 then TERADATA will save it in integer format and value will be
SEL (2013-1900)*10000 + 03*100 + 13 –> 
Example: If we have a table which has a DATE column DOA and it has some value as '2013-03-13' then we can fetch records like this:
SELECT * FROM EMPLOYEE WHERE DOJ='2013-03-13';
SELECT * FROM EMPLOYEE WHERE DOJ=1130313;
Century Break in Teradata
Chances of you encountering any error related to "Century break" is very less but if you do then it may take sometime to debug if you are not aware of this setting. In Teradata, whenever you get YEAR in DATE as a 2 digit number i.e. YY then Teradata has to determine the complete year for that date. Example if the DATE is '20-01-01' and format is 'YY-MM-DD' then Teradata has to determine whether 20 means 1920 or 2020.
As per current setting in Teradata in most environment if YY falls with-in range 00-29 then it is 20XX else it is 19XX. So remember this setting and it can be easily changed with the help of Teradata DBA.
Some more common example of Teradata Date Functions & Formats
Teradata current date
To select current date in teradata you can use "current_date" or even just "date".
select current_date as c1 ,date as c2; c1 c2 2020-10-30 2020-10-30
However I will suggest to use "current_date" over "date". The reason is sometimes in SQL query you may want to format DATE columns as well. Now for a beginner in SQL , DATE FORMAT and DATE as current date can lead to some confusion. Check the example below:
select cast(cast(date as date format 'dd-mmmm-yyyy') as varchar(20)) as c1, cast(cast(current_date as date format 'dd-mmmm-yyyy') as varchar(20)) as c2; c1 c2 30-October-2020 30-October-2020
The first column "c1" may be slightly confusion for SQL beginner which has "date as date" in it. So my recommendation is to use current_date to select current date in Teradata. Also it is more standard which works in other databases as well.
Add months in Teradata
You can use add_months function in teradata to add/subtract months from any date value in teradata. In the example below we have fetched current date, added 2 months to current date and subtracted 2 months to current date.
select current_date as c1, add_months(current_date,2) as c2, add_months(current_date,-2) as c3; c1 c2 c3 2020-10-30 2020-12-30 2020-08-30
You can also add months in teradata date by using the interval example which we have mentioned in the table above by adding 2 months to date. However I will recommend to use add_months function if you are handling last date of the months. The reason is if you are using INTERVAL and adding months then it may result in invalid date. However add_months is smart function and takes care of boundary value for any given date. Let's look at the example below:
ADD_MONTHS is smart function that has handle date properly and does not result in "30th-Feb" as output which would have resulted in error.
select current_date as c1, add_months(current_date,4) as c2; c1 c2 2020-10-30 2021-02-28
Now lets try with INTERVAL and add 4 months to current date to see output
select current_date as c1, current_date + interval '4' month as c2; ERROR: Invalid Date
Error Reason: February does not have 30 days in it i.e. 2021-02-30 is invalid date.
Hence I will advise to use add_months function when handling last date of month type problems in teradata.
Teradata last day of month
To get the last day of current month, go to next month and subtract number of days from it. Example
SELECT current_date as c1, (ADD_MONTHS(current_date,1)) - EXTRACT(DAY FROM ADD_MONTHS(current_date,1)) as c2; c1 c2 2020-10-30 2020-10-31
This approach shall take care of boundary values like FEBRUARY which does not have 30,31 days in it.
So in this post we have seen lots of examples to handle different date format and functions available in teradata. Hope this helps.
List of common Teradata date functions and formats
|Date Function||Output||Return Type||Description|
|SELECT DATE||2020-10-09||DATE||Fetch Current Date|
|SELECT CAST(DATE AS VARCHAR(20))||20/10/09||VARCHAR(20)||SQL Editor may modify the DATE column and display date value in some other format.To Display DATE in actual format use casting.|
|SELECT CAST(DATE AS DATE FORMAT 'Y4-MM-DD')||2020-10-09||DATE||Display date in the user specified format|
|SELECT DATE + INTERVAL '2' DAY||2020-10-11||DATE||Add 2 Days to DATE|
|SELECT DATE + INTERVAL '2' MONTH||2020-12-09||DATE||Add 2 Months to DATE|
|SELECT DATE + INTERVAL '2' YEAR||2022-10-09||DATE||Add 2 Years to DATE|
|SELECT DATE – (DATE -2 )||2||INTEGER||Subtract 2 DATEs|
|SELECT CAST(DATE AS TIMESTAMP(0))||2020-10-09 00:00:00||TIMESTAMP(0)||Change Date to Timestamp|
|SELECT EXTRACT(YEAR FROM DATE)||2020||INTEGER||Select YEAR from Date|
|SELECT EXTRACT(MONTH FROM DATE)||10||INTEGER||Select MONTH from Date|
|SELECT EXTRACT(DAY FROM DATE)||9||INTEGER||Select DAY from Date|
|SELECT (DATE – EXTRACT(DAY FROM DATE))||2020-09-30||DATE||Select LAST DAY of LAST MONTH|
|SELECT (DATE – EXTRACT(DAY FROM DATE)) + 1||2020-10-01||DATE||Select FIRST day of Current Month|
|SELECT (DATE + INTERVAL '1' MONTH) – EXTRACT(DAY FROM ADD_MONTHS(DATE,1))||2020-10-31||DATE||Select LAST day of Current Month|
|SELECT TD_DAY_OF_WEEK(DATE)||6||INTEGER||Day of the Week. [Friday]. Week starts from Sunday i.e. 1 and ends at Saturday i.e. 7|
|SELECT TD_DAY_OF_MONTH(DATE)||9||INTEGER||Day of the Month|
|SELECT TD_DAY_OF_YEAR(DATE)||283||INTEGER||203 day of the year out of 365/366 days in a year.|
|SELECT TD_DAY_OF_CALENDAR(DATE)||44112||INTEGER||days since 01-January-1900|
|SELECT TD_WEEKDAY_OF_MONTH(DATE)||2||INTEGER||2nd Friday of Month|
|SELECT TD_WEEK_OF_MONTH(DATE)||1||INTEGER||1st Full Week of the month. The previous week didn't start in this month. Sunday is considered as start of week.|
|SELECT TD_WEEK_OF_YEAR(DATE)||40||INTEGER||40th Full Week of the year.|
|SELECT TD_WEEK_OF_CALENDAR(DATE)||6301||INTEGER||6301th Full Week of the CALENDAR i.e. since 01-January-1900.|
|SELECT TD_MONTH_OF_QUARTER(DATE)||1||INTEGER||1st month of Quarter|
|SELECT TD_MONTH_OF_YEAR(DATE)||10||INTEGER||10th Month of Year|
|SELECT TD_MONTH_OF_CALENDAR(DATE)||1450||INTEGER||1450th month of CALENDAR. i.e. since 01-January-1900|
|SELECT TD_QUARTER_OF_YEAR(DATE)||4||INTEGER||4th Quarter of the Year|
|SELECT TD_QUARTER_OF_CALENDAR(DATE)||484||INTEGER||484th Quarter of CALENDAR i.e since 01-January-1900|
|SELECT TD_YEAR_OF_CALENDAR(DATE)||2020||INTEGER||Year of CALENDAR|