Teradata Date Functions

teradata

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.

Below are few example on date functions and formats in teradata

Date FunctionOutputReturn TypeDescription
SELECT DATE2020-10-09DATEFetch Current Date
SELECT CAST(DATE AS VARCHAR(20))20/10/09VARCHAR(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-09DATEDisplay date in the user specified format
SELECT DATE + INTERVAL ‘2’ DAY2020-10-11DATEAdd 2 Days to DATE
SELECT DATE + INTERVAL ‘2’ MONTH2020-12-09DATEAdd 2 Months to DATE
SELECT DATE + INTERVAL ‘2’ YEAR2022-10-09DATEAdd 2 Years to DATE
SELECT DATE – (DATE -2 )2INTEGERSubtract 2 DATEs
SELECT CAST(DATE AS TIMESTAMP(0))2020-10-09 00:00:00TIMESTAMP(0)Change Date to Timestamp
SELECT EXTRACT(YEAR FROM DATE)2020INTEGERSelect YEAR from Date
SELECT EXTRACT(MONTH FROM DATE)10INTEGERSelect MONTH from Date
SELECT EXTRACT(DAY FROM DATE)9INTEGERSelect DAY from Date
SELECT (DATE – EXTRACT(DAY FROM DATE))2020-09-30DATESelect LAST DAY of LAST MONTH
SELECT (DATE – EXTRACT(DAY FROM DATE)) + 12020-10-01DATESelect FIRST day of Current Month
SELECT (DATE + INTERVAL ‘1’ MONTH) – EXTRACT(DAY FROM ADD_MONTHS(DATE,1))2020-10-31DATESelect LAST day of Current Month
SELECT TD_DAY_OF_WEEK(DATE)6INTEGERDay of the Week. [Friday]. Week starts from Sunday i.e. 1 and ends at Saturday i.e. 7
SELECT TD_DAY_OF_MONTH(DATE)9INTEGERDay of the Month
SELECT TD_DAY_OF_YEAR(DATE)283INTEGER203 day of the year out of 365/366 days in a year.
SELECT TD_DAY_OF_CALENDAR(DATE)44112INTEGERdays since 01-January-1900
SELECT TD_WEEKDAY_OF_MONTH(DATE)2INTEGER2nd Friday of Month
SELECT TD_WEEK_OF_MONTH(DATE)1INTEGER1st 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)40INTEGER40th Full Week of the year.
SELECT TD_WEEK_OF_CALENDAR(DATE)6301INTEGER6301th Full Week of the CALENDAR i.e. since 01-January-1900.
SELECT TD_MONTH_OF_QUARTER(DATE)1INTEGER1st month of Quarter
SELECT TD_MONTH_OF_YEAR(DATE)10INTEGER10th Month of Year
SELECT TD_MONTH_OF_CALENDAR(DATE)1450INTEGER1450th month of CALENDAR. i.e. since 01-January-1900
SELECT TD_QUARTER_OF_YEAR(DATE)4INTEGER4th Quarter of the Year
SELECT TD_QUARTER_OF_CALENDAR(DATE)484INTEGER484th Quarter of CALENDAR i.e since 01-January-1900
SELECT TD_YEAR_OF_CALENDAR(DATE)2020INTEGERYear of CALENDAR

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 –> [1130313]

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′;

OR

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;

OUTPUT:
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

OUTPUT:
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

OUTPUT:
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;
	
OUTPUT:
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

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

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

25 thoughts on “Teradata Date Functions”

  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

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

  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.

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

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

  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

  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

  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.

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

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

    1. 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 ? šŸ˜›

    2. 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 ? šŸ™‚

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

Leave a Reply

Your email address will not be published. Required fields are marked *