Teradata Date Functions
Date consists of 3 parts – Day , Month & Year. When we combine all 3 parts together we get the output of DATE datatype. In Teradata, any function which you can apply on DATE datatype can be considered as DATE Function.
In my opinion, Teradata Date Functions can be classified into two types broadly :
First can be Date functions that change the DATE in appearance only. This can be like EXTRACT portion of date or change FORMAT of date value. So the value is same , it just looks different.
Second type is where you apply some maths to get a new date from existing date. This can be like adding , subtracting days , months to DATE. The final value is different than the original date value.
In this post, we will see different data functions in Teradata with syntax and examples.
Teradata Date Functions – Table Setup for Practice
I have used below sample table for all the examples mentioned in this post. You can easily create it and then run queries for practice.
teradata create table date format
You can easily specify the date format you want for DATE column while creating table in Teradata.
create volatile table vt_date_function
(
start_date date format 'yyyy-mm-dd',
end_date date format 'yyyyy-mm-dd'
)
on commit preserve rows;
insert into vt_date_function values ('2021-01-01','2021-12-31');
insert into vt_date_function values ('2022-01-01','2022-12-31');
select start_date,end_date from vt_date_function ;
start_date end_date
---------- ----------
2022-01-01 2022-12-31
2021-01-01 2021-12-31Teradata Date Functions – Current Date
When ever you have to get the current date in your SQL query , you will use current_date function. This function returns the today’s date. Current Date is very frequently used to calculate Date Difference between given date and today date.
select current_date as today_date; today_date 2021-05-29
Teradata Date Functions – Type 1
In Type 1, Date Functions we will see different formats for any given date. How we can change date from one format to another. We will also cover different date functions which can help in showing only portion of DATE value.
Teradata Date Format
You can change appearance of DATE value by using FORMAT keyword to cast it into different date format. So the value remains same however the date will look different in appearance only.
You can use below Date Formats in Teradata for Year , Month & Day type to get desired Date output.
| # | Type | Format | Value | Description |
|---|---|---|---|---|
| 1 | Year | YY | 14 | 2 digits of century |
| 2 | Year | YYYY | 2014 | complete year |
| 3 | Year | Y4 | 2014 | complete year |
| 4 | Month | MM | 11 | 2 digit month |
| 5 | Month | MMM | Nov | short form of month |
| 6 | Month | MMMM | November | full name of month |
| 7 | Day | DD | 12 | 2 digit date |
| 8 | Day | DDD | 316 | day of the year |
| 9 | Blank | B | blank |
You can create any number of combinations to represent date in desired format in teradata using the list mentioned above.
NOTE: If you are using SQL editor like Teradata SQL Assistant, Teradata Studio, DBeaver then these editor tend to change the date format in result-set. So in display you may see some other default format for DATE columns however actual format might be different. To check the actual date format just cast the date to varchar(10) and then see the date output.
General syntax for Teradata Date Format
cast(date_column as DATE FORMAT ‘xxxxx’)
where date_coumn is DATE value or DATE column from the table , FORMAT specifies the look of output DATE value.
Let’s look at different examples below for Teradata DATE FORMAT
teradata cast date format dd/mm/yyyy
select start_date, cast(start_date as date format 'dd/mm/yyyy') as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 01/01/2022 2021-01-01 01/01/2021
teradata cast date format yyyymmdd
select start_date, cast(start_date as date format 'yyyymmdd') as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 20220101 2021-01-01 20210101
teradata date format yyyymm
You can convert date to yyyymm in teradata easily by using the date format. You can see that we have omitted DAY component from the output easily by using DATE FORMAT.
select start_date, cast(start_date as date format 'yyyymm') as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 202201 2021-01-01 202101
teradata date format mm dd yyyy
In Teradata date format if you have to give space between Date components , use “B” to give blank.
select start_date, cast(start_date as date format 'mmBddByyyy') as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 01 01 2022 2021-01-01 01 01 2021
teradata date format yyyy mm dd
select start_date, cast(start_date as date format 'yyyyBmmBdd') as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 2022 01 01 2021-01-01 2021 01 01
teradata convert string to date
In Teradata, you can easily convert string to DATE. If the string is mentioned in default date format then it is implicit conversion else you must specify the date format which corresponds to string DATE value.
select cast('2021-08-20' as date format 'yyyy-mm-dd') as c1;
c1
----------
2021-08-20If the date format is not same as STRING value then the date conversion may fail.
select cast('21/08/20' as date format 'yyyy-mm-dd') as c1;
*** Failure 2665 Invalid date.
Statement# 1, Info =0
*** Total elapsed time was 1 second.In most of the cases, string to date conversion shall be taken care by implicit conversion with error. However in some cases you may have to explicitly give correct format to get right DATE output.
select cast('21/08/20' as date format 'yy/mm/dd') as c1;
c1
--------
21/08/20
Change Date Format in Teradata
You can easily convert date from one format to another. This is achieved by using nested “CAST AS DATE” functions.
select start_date, cast(start_date as date format 'mmBddByyyy') as start_date_new, cast(cast(start_date as date format 'mmBddByyyy') as date format 'yyyy/mm/dd') as start_date_new2 from vt_date_function ; start_date start_date_new start_date_new2 ---------- -------------- --------------- 2022-01-01 01 01 2022 2022/01/01 2021-01-01 01 01 2021 2021/01/01
teradata date format yy conversion
When you have date format in “yy” mode i.e. just 2 digits representation for year be very careful while converting into four digits year format.
select cast(cast('21/08/20' as date format 'yy/mm/dd') as date format 'yyyy-mm-dd') as c1;
c1
----------
1921-08-20You may be expecting 2021 in place of 1921 in output. So be careful while handling 2 digit representation of YEAR in Teradata. You may also want to read about Teradata Date Century Break too.Read this post for more details.
teradata date format in where clause
You can also use date format in where clause while applying filter on DATE columns. If the date format is not default then it is safe practice to apply date format using cast on both sides.
select start_date , end_date
from vt_date_function
where cast(end_date as date format 'yyyy/mm/dd') = cast('2022/12/31' as date format 'yyyy/mm/dd');
start_date end_date
---------- ----------
2022-01-01 2022-12-31You can see in above result only 1 row is displayed in the output.
teradata month name from date
You can easily convert Date to month name in Teradata by specifying proper DATE FORMAT like ‘MMM’ for shorthand month name or ‘MMMM’ for full month name.
select start_date, start_date (format 'MMM') as month_name from vt_date_function ; start_date month_name ---------- ---------- 2022-01-01 Jan 2021-01-01 Jan select start_date, start_date (format 'MMMM') as month_name from vt_date_function ; start_date month_name ---------- ---------- 2022-01-01 January 2021-01-01 January
Teradata Date Functions – Type 2
In Type 2 Date Functions we will see examples where the output DATE value is different from input DATE. We will apply some functions on Input DATE and will get some other DATE as output. This does not mean that DATE FORMAT will be different all the time but DATE value shall be something else.
teradata add days
You can easily add days to existing date by using add operator to DATE value.
select start_date, start_date + 10 (format 'yyyy-mm-dd') as add_days from vt_date_function ; start_date add_days ---------- ---------- 2022-01-01 2022-01-11 2021-01-01 2021-01-11
In the above example, we have added 10 days to existing date value. You can also add INTERVAL 10 days to achieve same result.
select start_date, start_date + interval '10' day (format 'yyyy-mm-dd') as add_days from vt_date_function ; start_date add_days ---------- ---------- 2022-01-01 2022-01-11 2021-01-01 2021-01-11
teradata add months
Teradata Date Function “add_months” is used to add given number of months to input date. If you want to add months to date then use add_months function.
select start_date, add_months(start_date,4) as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 2022-05-01 2021-01-01 2021-05-01
You can also pass negative number to actually “subtract” months by using add_months function in Teradata. In the above example we have added 4 months to given date. In the example below we have subtracted 4 months by passing negative number i.e. -4.
select start_date, add_months(start_date,-4) as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 2021-09-01 2021-01-01 2020-09-01
teradata last day of month
You can use last_day function in Teradata to get the last day of month for any given date.
select start_date, last_day(start_date) as start_date_new from vt_date_function ; start_date start_date_new ---------- -------------- 2022-01-01 22/01/31 2021-01-01 21/01/31
If your Teradata version does not have last_day function then you can use traditional method to compute last_day in Teradata.
SELECT start_date, (ADD_MONTHS(start_date,1)) - EXTRACT(DAY FROM ADD_MONTHS(start_date,1)) as last_day from vt_date_function ; start_date last_day ---------- -------- 2022-01-01 22/01/31 2021-01-01 21/01/31
You can also cast the output to suitable DATE format.
SELECT start_date, cast((ADD_MONTHS(start_date,1)) - EXTRACT(DAY FROM ADD_MONTHS(start_date,1)) as date format 'yyyy-mm-dd') as last_day from vt_date_function ; start_date last_day ---------- ---------- 2022-01-01 2022-01-31 2021-01-01 2021-01-31
teradata first day of month
You can use TRUNC method to truncate DATE value to the nearest DAY, MONTH or YEAR value.
select end_date, TRUNC(end_date, 'MM') (FORMAT 'yyyy-mm-dd') as first_day from vt_date_function ; end_date first_day ---------- ---------- 2022-12-31 2022-12-01 2021-12-31 2021-12-01
If your Teradata version does not have TRUNC date function then you can use traditional method.
select end_date, (end_date - EXTRACT(DAY FROM end_date)) + 1 (FORMAT 'yyyy-mm-dd') as first_day from vt_date_function ; end_date first_day ---------- ---------- 2022-12-31 2022-12-01 2021-12-31 2021-12-01
You can extract DATE components from DATE using “EXTRACT” function.
teradata first day of previous month
In the above query, in which we have fetched first day of the month, we can subtract 1 month to get first day of previous month.
select end_date, add_months((end_date - EXTRACT(DAY FROM end_date)) + 1,-1) (FORMAT 'yyyy-mm-dd') as first_day_prev_month from vt_date_function ; end_date first_day_prev_month ---------- -------------------- 2022-12-31 2022-11-01 2021-12-31 2021-11-01
extract day from date teradata
select end_date, EXTRACT(DAY FROM end_date) as month_date from vt_date_function ; end_date month_date ---------- ----------- 2022-12-31 31 2021-12-31 31
extract month from date teradata
select end_date, EXTRACT(MONTH FROM end_date) as month_date from vt_date_function ; end_date month_date ---------- ----------- 2022-12-31 12 2021-12-31 12
extract year from date in teradata
select end_date, EXTRACT(YEAR FROM end_date) as year_date from vt_date_function ; end_date year_date ---------- ----------- 2022-12-31 2022 2021-12-31 2021
teradata day of week
You can use “TD_DAY_OF_WEEK” function to return the number which corresponds to the day of the week. 1 means Sunday, 2 means Monday …7 means Saturday.
select end_date, TD_DAY_OF_WEEK(end_date) as day_week from vt_date_function ; end_date day_week ---------- ----------- 2022-12-31 7 2021-12-31 6
teradata days between two dates
You can directly subtract two date values to find the difference between two dates. Make sure that you give end_date first and then start_date to get correct output.
select end_date, start_date, end_date - start_date as day_difference from vt_date_function ; end_date start_date day_difference ---------- ---------- -------------- 2022-12-31 2022-01-01 364 2021-12-31 2021-01-01 364
More Teradata Date Function examples
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.
List of Teradata Date Functions
| Teradata Date Function | Output | Description |
|---|---|---|
| SELECT DATE | 2023-10-09 | Fetch Current Date |
| SELECT CAST(DATE AS VARCHAR(20)) | 23/10/09 | 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’) | 2023-10-09 | Display date in the user specified format |
| SELECT DATE + INTERVAL ‘2’ DAY | 2023-10-11 | Add 2 Days to DATE |
| SELECT DATE + INTERVAL ‘2’ MONTH | 2023-12-09 | Add 2 Months to DATE |
| SELECT DATE + INTERVAL ‘2’ YEAR | 2025-10-09 | Add 2 Years to DATE |
| SELECT DATE – (DATE -2 ) | 2 | Subtract 2 DATEs |
| SELECT CAST(DATE AS TIMESTAMP(0)) | 2023-10-09 00:00:00 | Change Date to Timestamp |
| SELECT EXTRACT(YEAR FROM DATE) | 2023 | Select YEAR from Date |
| SELECT EXTRACT(MONTH FROM DATE) | 10 | Select MONTH from Date |
| SELECT EXTRACT(DAY FROM DATE) | 9 | Select DAY from Date |
| SELECT (DATE – EXTRACT(DAY FROM DATE)) | 2023-09-30 | Select LAST DAY of LAST MONTH |
| SELECT (DATE – EXTRACT(DAY FROM DATE)) + 1 | 2023-10-01 | Select FIRST day of Current Month |
| SELECT (DATE + INTERVAL ‘1’ MONTH) – EXTRACT(DAY FROM ADD_MONTHS(DATE,1)) | 2023-10-31 | Select LAST day of Current Month |
| SELECT TD_DAY_OF_WEEK(DATE) | 6 | 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 | Day of the Month |
| SELECT TD_DAY_OF_YEAR(DATE) | 283 | 203 day of the year out of 365/366 days in a year. |
| SELECT TD_DAY_OF_CALENDAR(DATE) | 44112 | days since 01-January-1900 |
| SELECT TD_WEEKDAY_OF_MONTH(DATE) | 2 | 2nd Friday of Month |
| SELECT TD_WEEK_OF_MONTH(DATE) | 1 | 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 | 40th Full Week of the year. |
| SELECT TD_WEEK_OF_CALENDAR(DATE) | 6301 | 6301th Full Week of the CALENDAR i.e. since 01-January-1900. |
| SELECT TD_MONTH_OF_QUARTER(DATE) | 1 | 1st month of Quarter |
| SELECT TD_MONTH_OF_YEAR(DATE) | 10 | 10th Month of Year |
| SELECT TD_MONTH_OF_CALENDAR(DATE) | 1450 | 1450th month of CALENDAR. i.e. since 01-January-1900 |
| SELECT TD_QUARTER_OF_YEAR(DATE) | 4 | 4th Quarter of the Year |
| SELECT TD_QUARTER_OF_CALENDAR(DATE) | 484 | 484th Quarter of CALENDAR i.e since 01-January-1900 |
| SELECT TD_YEAR_OF_CALENDAR(DATE) | 2023 | Year of CALENDAR |
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(‘2023-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(‘2023-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 2023-03-13 then TERADATA will save it in integer format and value will be
SEL (2023-1900)*10000 + 03*100 + 13 –> [1230313]
Example: If we have a table which has a DATE column DOA and it has some value as ‘2023-03-13’ then we can fetch records like this:
SELECT * FROM EMPLOYEE WHERE DOJ=’2023-03-13′;
OR
SELECT * FROM EMPLOYEE WHERE DOJ=1230313;
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.
Century break creates issues while migrating from Teradata to some other database which has different YEAR set as century break year.
I will recommend not to use YEAR in YY format as much as possible.
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 2023-10-30 2023-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-2023 30-October-2023The 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 2023-10-30 2023-12-30 2023-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 2023-10-30 2024-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. 2023-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 2023-10-30 2023-10-31
This approach shall take care of boundary values like FEBRUARY which does not have 30,31 days in it.
Fix invalid date error in teradata
Invalid Date error in Teradata is one of the most common date related error we face while loading data from Source to Target. The error is because either the source is not sending date values in format which is same as Target or the date value is not correct. Eg: If Target is expecting date in format ‘YYYY-MM-DD’ then ‘2015-02-31’ is date in format ‘YYYY-MM-DD’ however the value is not correct as February cannot have 31 as date. Other example could be ‘2015/02/25’ is date with not same format as Target however value is correct. Both the error are pretty common. Now let us see how to identify date values which may be correct format wise however are incorrect in value.
CREATE MULTISET VOLATILE TABLE vt_date ( out_date VARCHAR(10) ) PRIMARY INDEX(out_date) ON COMMIT PRESERVE ROWS; INSERT INTO vt_date SEL '2015-05-17'; /* valid date */ INSERT INTO vt_date SEL '2015-17-05'; /* invalid date - month cannot be more than 12 */ INSERT INTO vt_date SEL '2015-01-32'; /* invalid date - date cannot be more than 31 */
We will use sys_calendar.calendar to identify the wrong date values. If you data set that ranges for date not present in “calendar” view then you can create a custom table and use that. But chances of this happening is very less.
SEL tb1.out_date FROM vt_date tb1 LEFT OUTER JOIN sys_calendar.calendar tb2 ON tb1.out_date = CAST(CAST(tb2.calendar_date AS DATE FORMAT 'y4-mm-dd') AS VARCHAR(10)) WHERE tb2.calendar_date IS NULL; --Output --2015-17-05 --2015-01-32
Now we have identified invalid dates, it is time to fix it. There are different ways by which you can fix this issue. Either you can remove such entries or you can fix it. And easiest method to fix it is to assign some default value to erroneous values.
UPDATE vt_date SET out_date = '1900-01-01' /* assign default values */ WHERE out_date IN (SEL tb1.out_date FROM vt_date tb1 LEFT OUTER JOIN sys_calendar.calendar tb2 ON tb1.out_date = CAST(CAST(tb2.calendar_date AS DATE FORMAT 'y4-mm-dd') AS VARCHAR(10)) WHERE tb2.calendar_date IS NULL);
Leave a Reply