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.
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”
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
Hi Ly
Kindly share the value of END_DATE column for which you are getting this error.
Best
Raj
Nice to read. Thank you
Hi Can we get month number if we have week number and Year number
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
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.
Hi Mano
Please explain your query in detail.
Which field are you talking about ?
Thanks
Raj
How can we do a where condition between (first day of last month) and (last day of last month)
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
Hi i need the queey,
Get the data from table 2011 to 2016 every month augest data.
Thanks
Does anybody try get last Friday from previous week?
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
Nice post, really appreciate your time to share!
MONTH(CURRENT_DATE + 300)
will it work or not ?
do we have any this type of function in teradata any version?
yes it works
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
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
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.
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.
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!!
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 ? 😛
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 ? 🙂
good post
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
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.