Teradata provides a way to truncate dates to the start of the week, month, quarter and year. This can achieved using inbuilt TRUNC function.
Syntax: TRUNC(date_column, Trunc_keyword)
Trunc_keyword |
Description |
Y |
Truncate date to start of the year (Example: 2018-09-10 to 2018-01-01) |
YEAR |
Truncate date to start of the year (Example: 2018-09-10 to 2018-01-01) |
Q |
Truncate day to start of the Quarter (Example: 2018-09-10 to 2018-07-01) |
RM |
Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01) |
MON |
Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01) |
MONTH |
Truncate day to start of the month (Example: 2018-09-10 to 2018-09-01) |
DAY |
Truncate day to start of the week |
D |
Truncate day to start of the week |
W |
Truncate date to day of the week which is same as first day of month |
WW |
Truncate date to day of the week which is same as first day of year |
--Example 1
SELECT TRUNC(CURRENT_DATE, 'YEAR'),CURRENT_DATE;
Output:
TRUNC(Current Date,'YEAR') Current Date
-------------------------- ------------
2018-01-01 2018-10-10
--Example 2
SELECT TRUNC(CURRENT_DATE, 'Y'),CURRENT_DATE;
Output:
TRUNC(Current Date,'Y') Current Date
----------------------- ------------
2018-01-01 2018-10-10
--Example 1
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'YEAR'),12)-1 Last_day_of_year ,CURRENT_DATE;
Output:
Last_day_of_year Current Date
---------------- ------------
2018-12-31 2018-10-10
--Example 2
SELECT ADD_MONTHS(TRUNC(CAST('2018-05-23' AS DATE), 'YEAR'),12)-1 Last_day_of_year;
Last_day_of_year
----------------
2018-12-31
SELECT TRUNC(CURRENT_DATE, 'Q'),CURRENT_DATE;
Output:
TRUNC(Current Date,'Q') Current Date
----------------------- ------------
2018-10-01 2018-10-10
SELECT ADD_MONTHS(TRUNC(CURRENT_DATE, 'Q'),3)-1 Last_day_of_quarter, CURRENT_DATE;
Output:
Last_day_of_quarter Current Date
------------------- ------------
2018-12-31 2018-10-10
--Example 1
SELECT TRUNC(CURRENT_DATE, 'MON'), CURRENT_DATE;
Output:
TRUNC(Current Date,'RM') Current Date
------------------------ ------------
2018-10-01 2018-10-10
--Example 2
SELECT TRUNC(CURRENT_DATE, 'MONTH'), CURRENT_DATE;
Output:
TRUNC(Current Date,'RM') Current Date
------------------------ ------------
2018-10-01 2018-10-10
--Example 3
SELECT TRUNC(CURRENT_DATE, 'RM'), CURRENT_DATE;
Output:
TRUNC(Current Date,'RM') Current Date
------------------------ ------------
2018-10-01 2018-10-10
SELECT TRUNC(ADD_MONTHS(CURRENT_DATE, -1), 'MONTH') First_day_of_last_month, CURRENT_DATE;
Output:
First_day_of_last_month Current Date
----------------------- ------------
2018-09-01 2018-10-10
SELECT LAST_DAY(CURRENT_DATE) Last_day_of_month, CURRENT_DATE;
Output:
Last_day_of_month Current Date
----------------- ------------
2018-10-31 2018-10-10
SELECT TRUNC(CURRENT_DATE, 'MONTH')-1 Last_day_of_last_month, CURRENT_DATE;
Output:
Last_day_of_last_month Current Date
---------------------- ------------
2018-09-30 2018-10-10
--Example 1
SELECT TRUNC(CURRENT_DATE, 'D'), CURRENT_DATE;
Output:
TRUNC(Current Date,'D') Current Date
----------------------- ------------
2018-10-07 2018-10-10
--Example 2
SELECT TRUNC(CURRENT_DATE, 'DAY'), CURRENT_DATE;
Output:
TRUNC(Current Date,'DAY') Current Date
------------------------- ------------
2018-10-07 2018-10-10
--Example 1
SELECT TRUNC(CURRENT_DATE + 7, 'D') - 1 Last_day_week, CURRENT_DATE;
Output:
Last_day_week Current Date
------------- ------------
2018-10-13 2018-10-10