Handle Teradata Date Functions like pro – the ultimate guide

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-31

Teradata 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.

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-20

If 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-20

You 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-31

You 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

I have tried to cover most of the Teradata Date Functions and Teradata Date Formats used in SQL. Also I will strongly recommend to read below post which shall cover all the date related information and you may need not look for any more information after that.

Read More : Teradata Date Functions – This post covers the basics of DATE. How it is stored internally with many examples.