Teradata Date Functions

In Teradata, there are various functions available for DATE columns. I have seen many people getting confused and committing mistakes  while handling columns of DATE datatype. So listing down few of the most basic methods of handling DATE columns.

To Fetch Current Date:
SELECT DATE;
TERADATA SQL Assistant modifies the DATE column and display in format specified in settings.To Display DATE in actual format:
SELECT CAST(DATE AS VARCHAR(20));
Display date in the user specified format
SELECT CAST(DATE AS DATE FORMAT ‘Y4-MM-DD’);
Add 2 Days to DATE
SELECT DATE + INTERVAL ‘2’ DAY;
Add 2 Months to DATE
SELECT DATE + INTERVAL ‘2’ MONTH;
Add 2 Years to DATE
SELECT DATE + INTERVAL ‘2’ YEAR;
Subtract 2 DATEs
SELECT DATE – (DATE -2 );
Change Date to Timestamp
SELECT CAST(DATE AS TIMESTAMP(0));
Select YEAR from Date
SELECT EXTRACT(YEAR FROM DATE);
Select MONTH from Date
SELECT EXTRACT(MONTH FROM DATE);
Select DAY from Date
SELECT EXTRACT(DAY FROM DATE);
Select LAST DAY of LAST MONTH
SELECT (DATE – EXTRACT(DAY FROM DATE))
Select FIRST day of Current Month
SELECT (DATE – EXTRACT(DAY FROM DATE)) + 1;
Select LAST day of Current Month
SELECT (DATE + INTERVAL ‘1’ MONTH) – EXTRACT(DAY FROM ADD_MONTHS(DATE,1));
SELECT DATE IN SOME OTHER FORMAT THEN SPECIFIED: 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’);

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;

22 Comments

  1. we have table(sales_id,sales,sales_date)

    but i want last year every month last day sales
    and first day from evry month in the last yer sales details

    send me at rathnamch@gmail.com

    1. Author

      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.

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

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

      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 ? 🙂

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

      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.

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

  6. MONTH(CURRENT_DATE + 300)
    will it work or not ?
    do we have any this type of function in teradata any version?

  7. Nice post, really appreciate your time to share!

  8. Does anybody try get last Friday from previous week?

    1. Author

      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

  9. How can we do a where condition between (first day of last month) and (last day of last month)

    1. Author

      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 i need the queey,

        Get the data from table 2011 to 2016 every month augest data.

        Thanks

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

      Hi Mano
      Please explain your query in detail.
      Which field are you talking about ?

      Thanks
      Raj

  11. Hi Can we get month number if we have week number and Year number

    1. Author

      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

Leave a Reply

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