Various Date Functions are avaialble in Teradata. For example: date, NEXT_DAY, LAST_DAY, ROUND, TRUNC and Interval functions as well

Teradata Date Functions

• NEXT_DAY
• LAST_DAY
• ROUND
• TRUNC
• EXTRACT
• INTERVAL
• FORMAT
• Subtract Timestamps

• CURRENT_DATE: Returns the current date of the system.

  
  SELECT CURRENT_DATE;
  Output: 2016-08-23
  

  
  
• CURRENT_TIME: Returns the current time of the system with timezone.

  
  SELECT CURRENT_TIME;
  Output: 06:11:00+00:00
  

  
  
• TIME: Returns the current time of the system.

  
  SELECT TIME;
  Output: 23:56:22
  

  
  
• CURRENT_TIMESTAMP: Returns the current date/time of the system.

  
  SELECT CURRENT_TIMESTAMP;
  Output: 2016-08-23 23:56:22
  

  
  
• NEXT_DAY: Returns the next coming date whose day of the week is equivalent to mentioned day of week.

  
  SELECT NEXT_DAY(DATE '2016-11-06', 'TUESDAY'); -- 8th Nov is upcoming tuesday
  Output: 2016-11-08
  

  
  
• LAST_DAY: Returns the last day of the month against the given date.

  
  SELECT LAST_DAY(DATE '2016-11-06'); -- 30th Nov is last day of month
  Output: 2016-11-30
  

  
  
• ROUND: Returns the rounded date based on character_code passed.

  
  Syntax: ROUND(date_value,[character_code])
  
  SELECT ROUND(DATE '2016-11-09','D'); -- 6th Nov is the start day of 9th Nov week
  Output: 2016-11-06
  
  SELECT ROUND(DATE '2016-11-18','RM'); -- Since date is greater than 15(past half month), date is rounded to next month
  Output: 2016-12-01 
  
  SELECT ROUND(DATE '2016-11-18','Q'); -- Since date is greater than 45 days(past half quarter),date is rounded to next Quarter's 1st date
  Output: 2017-01-01 
  
  SELECT ROUND(DATE '2016-11-18','Y'); -- Since date is greater than June's date(past half year), date is rounded to next year's 1st date
  Output: 2017-01-01 
  
  

  
  
• TRUNC: Returns the truncated date based on character_code passed.

  
  Syntax: TRUNC(date_value,[character_code])
  
  SELECT TRUNC(DATE '2016-11-09','D'); -- 6th Nov is the start day of 9th Nov week
  Output: 2016-11-06
  
  SELECT TRUNC(DATE '2016-11-18','RM'); -- Truncated to the start of the month
  Output: 2016-11-01 
  

  
  
• EXTRACT: Can be used to extract Hour/Minute/Second/Day/Month/Year from timestamp/date/time.

  
  
  SELECT EXTRACT( YEAR FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 2016
  
  SELECT EXTRACT( MONTH FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 11
  
  SELECT EXTRACT( DAY FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 09
  
  SELECT EXTRACT( DAY FROM DATE '96-11-09');  -- Date not in Ansi format
  Output: Error
  
  SELECT EXTRACT( DAY FROM DATE '2016-02-30');  -- Invalid date
  Output: Error
  
  SELECT EXTRACT( HOUR FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 12
  
  SELECT EXTRACT( MINUTE FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 15
  
  SELECT EXTRACT( SECOND FROM TIMESTAMP '2016-11-09 12:15:19'); 
  Output: 19
  

  
  
• Interval Expression: can be used to add/subtract Hour/Minute/Second/Day/Month/Year to timestamp/date/time.

  
  SELECT TIMESTAMP'2016-11-09 12:15:19' + INTERVAL '1' SECOND;  -- Adding 1 second
  Output: 2016-11-09 12:15:20
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' - INTERVAL '1' MINUTE;  -- subracting 1 minute
  Output: 2016-11-09 12:14:19
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' + INTERVAL '2' HOUR;  -- Adding 2 HOUR
  Output: 2016-11-09 14:15:19
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' - INTERVAL '1' DAY;  -- subracting 1 Day
  Output: 2016-11-08 12:15:19
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' + INTERVAL '3' MONTH;  -- Adding 3 Month
  Output: 2017-02-09 12:15:19
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' + INTERVAL '2' YEAR;  -- Adding 2 Years
  Output: 2018-11-09 12:15:19
  
  SELECT TIMESTAMP'2016-11-09 12:15:19' + INTERVAL '12:30:15' HOUR TO SECOND;  -- Adding 12:30:15 to the timestamp
  Output: 2016-11-10 00:45:34 
   
  SELECT 10000* Interval '1' SECOND;  -- Interval fields can hold upto 4 digits only
  Output: *** Failure 7453 Interval field overflow. 
  
  SELECT CAST((9999* Interval '1' SECOND) AS INTERVAL HOUR TO MINUTE); -- Conveting number to time interval
  Output: 2:46 
  
  SELECT CAST((36600* Interval '00:00:01' HOUR TO SECOND) AS INTERVAL HOUR TO MINUTE)
  Output: 10:10
  

  
  
• Subtracting 2 timestamps

  
  SELECT (CURRENT_TIMESTAMP(0) - CAST('2016-11-06 12:15:19' AS TIMESTAMP(0))) hour(4) to second(4) ;
  Output: 18:02:17.0000
  

  Click here to check more option on Subtracting timestamp
  
  
  
• Date Formating: Date/Timestamps can be formated using 'FORMAT clause.

  
  Displaying AM/PM using 'T' & 'B' is used for space. 
  SELECT CAST(TIMESTAMP'2016-11-12 14:32:45' AS FORMAT 'yyyy-mm-ddbhh:mi:ssBt');   
  Output: 2016-11-12 02:32:45 PM
  
  
  Need to cast timestamp explicitly if string, else query will fail 
  SELECT '2016-11-12 14:32:45' (format 'E4,BDDB M4,YYYYBHH:MI:SSBT');
  Output: *** Failure 3527 Format string 'E3,BDDBM3,YYYYBHH:MI:SS' has combination of  numeric, character  and GRAPHIC values
  
  
  E4 used to show full day name & M4 used to show full month name
  SELECT TIMESTAMP'2016-11-12 14:32:45' (format 'E4,BDDB M4,YYYYBHH:MI:SSBT');  
  Output: Saturday, 12 November,2016 02:32:45 PM
  
  
  E3 used to show short day name & M3 used to show short month name
  SELECT TIMESTAMP'2016-11-12 14:32:45' (format 'E3,BDDB M3,YYYYBHH:MI:SS');
  Output: Sat, 12 Nov,2016 14:32:45
  
  SELECT CAST('2016-11-12' AS DATE) (format 'dd/mm/yyyy'); --Default format is YYYY-MM-DD
  Output: 12/11/2016
  

  Click here to check more option on timestamp/date Formatting