Teradata: First and Last Day

Pradeep

Teradata: First and Last Day

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)

Below tables show most of the metacharacters that can used for 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

All the below examples can be used with any DATE column (i.e CURRENT_DATE can be replaced with column of DATE datatype).

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

Last Day of Year


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

First Day of Quarter


SELECT TRUNC(CURRENT_DATE, 'Q'),CURRENT_DATE;

Output:
TRUNC(Current Date,'Q')  Current Date
-----------------------  ------------
             2018-10-01    2018-10-10

Last Day of Quarter


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

First Day of Month


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

First Day of Last Month


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

Last Day of Month


SELECT LAST_DAY(CURRENT_DATE) Last_day_of_month, CURRENT_DATE;

Output:
Last_day_of_month  Current Date
-----------------  ------------
       2018-10-31    2018-10-10

Last Day of Last Month


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

First Day of Week


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

Last Day of Week


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