TERADATA: Date and Time Functions

Date and Time functions

Monday, September 22, 2014

7:51 AM

FIRST and LAST Day of Month

SELECT CURRENT_DATE;

SELECT (CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1)                          AS "1st Day Cur Mo"

      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), -1)          AS "1st Day Prev1 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), -2)          AS "1st Day Prev2 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 1)           AS "1st Day Next1 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 2)           AS "1st Day Next2 Mo"
      ,LAST_DAY(CURRENT_DATE)                                                     AS "Last Day Cur Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-1)) AS "Last Day Prev1 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-2)) AS "Last Day Prev2 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 1)) AS "Last Day Next1 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 2)) AS "Last Day Next2 Mo"
;

Basic Date Functions

Display current date.
DATE or CURRENT_DATE

Display current time.
TIME or CURRENT_TIME

Display combination of both date and time.
CURRENT_TIMESTAMP

Add 3 months to the current date.
ADD_MONTHS(date,3)

Subtract 2 months from the current date.
ADD_MONTHS(date, -2)

EXTRACT Function:
EXTRACT(Year FROM Date)
EXTRACT(Month FROM Date)
EXTRACT(Day FROM Date)
EXTRACT(Hour FROM Time)
EXTRACT(Minute FROM Time)
EXTRACT(Second FROM Time)

Count by YYYY-MM:

SELECT CAST(EXTRACT(Year FROM READING_DATE_TS) AS CHAR(4))||'-'||
       SUBSTRING('00' FROM 1 FOR 2-CHARS(TRIM(EXTRACT(Month FROM READING_DATE_TS))))||
       TRIM(EXTRACT(Month FROM READING_DATE_TS)) AS YEAR_MTH
      ,COUNT(1) AS TOTAL
  FROM TABLE
GROUP BY 1
ORDER BY 1 DESC;