DATE Functions

As part of Domain Specific Functions Teradata database 14 has released new DATE functions.
Below re list of function.

Function	Description
LAST_DAY	Return date of the last day of the month that contains timestamp value
NEXT_DAY	returns first weekday named by day_value that is later than the date specified by date/timestamp value
NUMTODSINTERVAL	convert a numeric value into an INTERVAL DAY(4) TO SECOND(6) value
NUMTOYMINTERVAL	Convert a numeric value into an INTERVAL YEAR(4) TO MONTH value
TO_DSINTERVAL	Convert a string value into an INTERVAL DAY(4) TO SECOND(6) value.
TO_YMINTERVAL	Convert a string value into an INTERVAL YEAR(4) TO MONTH value.
MONTHS_BETWEEN	Return the number of months between two date/timestamp values.
OADD_MONTHS	Add a specified date/timestamp value to a specified number of months and return the resulting date.
TO_DATE	Convert a string into a DATE value via a format string.
TO_TIMESTAMP	Convert a string into a TIMESTAMP value via a format string.
TO_TIMESTAMP_TZ	Convert a string into a TIMESTAMP WITH TIME ZONE value via a format string.
TRUNC	Returns a DATE value with the time portion truncated to the unit specified by a format string.
ROUND	Returns a DATE value with the time portion rounded to the unit specified by a format string.

Examples:

SELECT LAST_DAY (DATE);
-----------------------
LAST_DAY(Date)
2014-06-30


SELECT NEXT_DAY(DATE '2014-06-10' , 'FRIDAY');
----------------------------------------------
NEXT_DAY(2014-06-10,'FRIDAY')
2014-06-13


SELECT NUMTODSINTERVAL(86405,'SECOND'), NUMTOYMINTERVAL(100, 'MONTH' );
----------------------------------------------------------------------------------------------------------------------
NUMTODSINTERVAL(86405,'SECOND')               NUMTOYMINTERVAL(100,'MONTH')
  1 00:00:05.000000                                                     8-04


SELECT TO_DSINTERVAL('150 08:30:00') , TO_YMINTERVAL( '2-11') ;
----------------------------------------------------------------------------------------------
TO_DSINTERVAL('150 08:30:00')                            TO_YMINTERVAL('2-11')
150 08:30:00.000000                                                       2-11
The above functions can be helpful while adding to any date columns.

samples
SELECT ORDER_DATE, ORDER_DATE + TO_YMINTERVAL('02-11') FROM ORDERS;
SELECT EMP_ID, LAST_NAME FROM EMP_TABLE  WHERE HIRE_DATE + TO_DSINTERVAL('100 00:00:00')


SELECT MONTHS_BETWEEN(DATE'2014-06-01', DATE'2014-02-01');
-------------------------------------------------------------
MONTHS_BETWEEN(2014-06-01,2014-02-01)
4.00


SELECT OADD_MONTHS (DATE '2014-04-15', 2), OADD_MONTHS (DATE '2008-02-29', 1);
---------------------------------------------------------------------------------------------------------------------
OADD_MONTHS(2014-04-15,2)                              OADD_MONTHS(2008-02-29,1)
2014-06-15                                             2008-03-31
Since 29 is the last day in February, March 31 is returned since 31 is the last day in March


SELECT TRUNC(CAST('2014/06/05' AS DATE), 'D') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------
TRUNC('2014/06/05','D')
2014-06-01
The date was rounded to the first day of that week.


SELECT ROUND(CAST('2003/09/20' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------------
ROUND('2003/09/20','RM')
2003-10-01
Since the day is greater than or equal to 16, the date is rounded to the beginning of the next month.