25 Teradata Date Functions for Beginners

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 FunctionOutputDescription
SELECT DATE2023-10-09Fetch Current Date
SELECT CAST(DATE AS VARCHAR(20))23/10/09SQL 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-09Display date in the user specified format
SELECT DATE + INTERVAL '2' DAY2023-10-11Add 2 Days to DATE
SELECT DATE + INTERVAL '2' MONTH2023-12-09Add 2 Months to DATE
SELECT DATE + INTERVAL '2' YEAR2025-10-09Add 2 Years to DATE
SELECT DATE – (DATE -2 )2Subtract 2 DATEs
SELECT CAST(DATE AS TIMESTAMP(0))2023-10-09 00:00:00Change Date to Timestamp
SELECT EXTRACT(YEAR FROM DATE)2023Select YEAR from Date
SELECT EXTRACT(MONTH FROM DATE)10Select MONTH from Date
SELECT EXTRACT(DAY FROM DATE)9Select DAY from Date
SELECT (DATE – EXTRACT(DAY FROM DATE))2023-09-30Select LAST DAY of LAST MONTH
SELECT (DATE – EXTRACT(DAY FROM DATE)) + 12023-10-01Select FIRST day of Current Month
SELECT (DATE + INTERVAL '1' MONTH) – EXTRACT(DAY FROM ADD_MONTHS(DATE,1))2023-10-31Select LAST day of Current Month
SELECT TD_DAY_OF_WEEK(DATE)6Day of the Week. [Friday]. Week starts from Sunday i.e. 1 and ends at Saturday i.e. 7
SELECT TD_DAY_OF_MONTH(DATE)9Day of the Month
SELECT TD_DAY_OF_YEAR(DATE)283203 day of the year out of 365/366 days in a year.
SELECT TD_DAY_OF_CALENDAR(DATE)44112days since 01-January-1900
SELECT TD_WEEKDAY_OF_MONTH(DATE)22nd Friday of Month
SELECT TD_WEEK_OF_MONTH(DATE)11st 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)4040th Full Week of the year.
SELECT TD_WEEK_OF_CALENDAR(DATE)63016301th Full Week of the CALENDAR i.e. since 01-January-1900.
SELECT TD_MONTH_OF_QUARTER(DATE)11st month of Quarter
SELECT TD_MONTH_OF_YEAR(DATE)1010th Month of Year
SELECT TD_MONTH_OF_CALENDAR(DATE)14501450th month of CALENDAR. i.e. since 01-January-1900
SELECT TD_QUARTER_OF_YEAR(DATE)44th Quarter of the Year
SELECT TD_QUARTER_OF_CALENDAR(DATE)484484th Quarter of CALENDAR i.e since 01-January-1900
SELECT TD_YEAR_OF_CALENDAR(DATE)2023Year of CALENDAR
Teradata Date Function Examples

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

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

Related Post : How to find and fix Invalid Date Error in Teradata

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.

So in this post we have seen lots of examples to handle different date format and functions available in teradata. Hope this helps.

Looking for more Teradata Date Functions & Teradata Date Formats examples ? Check our new post with more details and easy to understand examples.

Related Post : Teradata Date Functions – Syntax and Examples

25 thoughts on “25 Teradata Date Functions for Beginners”

  1. Hi.I have a table with the following data type as date
    ID DECIMAL(11,0),
    Acntg_Efctv_Date DATE FORMAT 'YYYY-MM-DD',
    Acntg_End_Date DATE FORMAT 'YYYY-MM-DD',
    Acntg_Efctv_ago DATE FORMAT 'YYYY-MM-DD',
    Acntg_Efctv_Date_fwd DATE FORMAT 'YYYY-MM-DD'
    Using a day ago and forward function it works for this field
    , Acntg_Efctv_Date + INTERVAL – '1' DAY AS Acntg_Efctv_ago
    , Acntg_Efctv_Date + INTERVAL '1' DAY AS Acntg_Efctv_Date_fwd
    However same function for the Acntg_End_Date I get error 2666.
    , Acntg_End_Date + INTERVAL – '1' DAY AS Acntg_End_Date_ago
    , Acntg_End_Date + INTERVAL '1' DAY AS Acntg_End_Date_fwd
    I've also tried to convert Cast(Cast(Acntg_End_Date AS CHAR(10)) AS DATE FORMAT 'YYYY-MM-DD')
    before calculation but still get error. Can anyone let me know why please?
    Thank you. Ly

    Reply
    • Hi Kanav
      Please try below query:
      select distinct month_of_year from sys_calendar.calendar where year_of_calendar=2017 and week_of_year=50;

      replace 2017 & 50 with actual values.


      Nitin

      Reply
  2. hi
    I am looking for getting pervious year (extract(year from DATES.CUR_ME_DT)-1)
    it works fine when the field is SMALLINT.
    how to tweak the given query or cast it.

    Reply
    • Hi Sukapati
      Try this:

      SEL CAST(DATE – EXTRACT(DAY FROM DATE) + 1 AS DATE) – INTERVAL '1' MONTH col1
      ,CAST(DATE + (30 – EXTRACT(DAY FROM DATE)) AS DATE) – INTERVAL '1' MONTH col2;

      Cheers
      Raj

      Reply
    • Hi Cris

      Try this:
      SEL tb1.calendar_date
      FROM sys_calendar.calendar tb1
      CROSS JOIN
      (SEL * FROM sys_calendar.calendar WHERE calendar_date=DATE) tb2
      WHERE tb1.week_of_calendar= tb2.week_of_calendar – 1
      AND tb1.day_of_week=6;

      Cheers
      Raj

      Reply
  3. Ooops… my comment was cut off. I plan on scheduling the report to run monthly for the past 12 months. Here are the two values I am trying to generate dynamically. If you know how this can be done I would appreciate your assistance.

    calendar.first_day_of_month >= 1st day of last month minue 1 year (Example September 01, 2012)
    calendar.first_day_of_month <= the last day of the last month (Example August 31, 2013)

    I think it should look something like this:
    Calendar.first_day_of_month <= (select cast(cast( CAST( EXTRACT(MONTH FROM foo_DATE) AS CHAR(2))||'01' as date format 'yyyymmdd') as date format 'yyyy-mm-dd'))

    Thanks again

    Thanks

    Reply
  4. Hi Raj – yes, I did read it. As I said in my post, this is for a business objects report using Teradata. That syntax does not work – it will not validate. I think it should look something like this:
    Calendar.first_day_of_month = 1st day of last month minue 1 year (Example September 01, 2012)
    calendar.first_day_of_month <= the last day of the last month (Example August 31, 2013)

    Thanks

    Reply
  5. I have a date field i.e END_DATE . I need to add 10 days to the END_DATE in where clause.
    Select * from A where Service_Date < END_DATE +10 days.both the columns are date data type.
    END_DATE DATE FORMAT 'YY/MM/DD' NOT NULL,.SERVICE_FROM_DATE DATE FORMAT 'YY/MM/DD' NOT NULL,
    Please let me know the query. I tried even below but it is not working .
    Select * from A where Service_Date < END_DATE + INTERVAL '10' DAY.

    Reply
    • Hi Gaffar

      "But it is not working ?" What does this mean ? You are getting error or you are not getting the output required ?
      Kindly share more information.
      If the date formats are same:
      Select * from A where Service_Date < END_DATE + INTERVAL '10' DAY this looks fine. Test it using below example: CREATE VOLATILE TABLE VT_TST_DT ( STRT_DT DATE FORMAT 'YY/MM/DD', END_DT DATE FORMAT 'YY/MM/DD' ) ON COMMIT PRESERVE ROWS; INSERT INTO VT_TST_DT SEL '13/05/13','13/05/23'; INSERT INTO VT_TST_DT SEL '13/05/13','13/05/15'; INSERT INTO VT_TST_DT SEL '13/05/13','13/05/01'; SEL * FROM VT_TST_DT WHERE STRT_DT< END_DT + INTERVAL '10' DAY; -- 2 rows returned.

      Reply
  6. Hi – I am wondering if you can help me? I am trying to write a query for a business objects report that will determine the last day of the last month. Right now I am prompting for it, but I want to generate it dynamically for scheduled reports.
    Right now I have:
    AND
    Calendar.first_day_of_month <= {d '2013-04-30'}
    I would like it to determine the last day of the last month automatically. Do you have an example of how I can do this?

    Thanks much!!

    Reply
    • Hi George

      You can try this : SELECT (DATE – EXTRACT(DAY FROM DATE))

      This was mentioned above in the post . Didn't you read the post completely ? 😛

      Reply
    • Hi George

      You can try this : SELECT (DATE – EXTRACT(DAY FROM DATE))

      This was mentioned above in the post . Didn't you read the post completely ? 🙂

      Reply
    • Hi Ratnam
      If I understand your requirement correctly, then you need sales of last DAY of each month and first day of that month. Try below query:

      SELECT
      SALES AS LAST_DAY_SALES,
      (SALES_DATE – EXTRACT(DAY FROM SALES_DATE)) + 1 AS FIRST_DAY
      FROM SALES_TABLE
      WHERE SALES_DATE = ((SALES_DATE + INTERVAL '1' MONTH) – EXTRACT(DAY FROM ADD_MONTHS(SALES_DATE,1)));

      Let me know if you need any clarity.

      Reply

Leave a Reply to Sukumar Cancel reply