Teradata Date Functions
Teradata Date Functions helps to perform variety of date/time/timestamp related operations.
- 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
- 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