Date and Time Functions in SAP HANA

ADD_DAYS

Syntax

 ADD_DAYS (d, n)

Description

Computes the date d plus n days.

Example

SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) "add days" FROM DUMMY;

Output For ADD_DAYS:

            04, jan 2010

ADD_MONTHS

Syntax

 ADD_MONTHS (d, n)

Description

Computes the date d plus n months.

Example

SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUMMY;

  

 Output For ADD MONTHS:

 05 jan, 2010

ADD_SECONDS

Syntax

 ADD_SECONDS (t, n)

Description

Computes the time t plus n seconds.

Example

SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 60*30) "add seconds" FROM DUMMY;

OUTPUT FOR ADD SECONDS

            2 Jan, 2012 12:00:45.0 AM

ADD_YEARS

Syntax

 ADD_YEARS (d, n)

Description

Computes the date d plus n years.

Example

SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY;

 

OUTPUT FOR ADD YEARS

5 Dec, 2010

CURRENT_DATE

Syntax

 CURRENT_DATE

Description

Returns the current local system date.

Example

SELECT CURRENT_DATE "current date" FROM DUMMY;

OUTPUT FOR CURRENT DATE

 31 Aug, 2017

CURRENT_TIME

Syntax

            CURRENT_TIME

Description

Returns the current local system time.

Example

Select current_time " CURRENT TIME" From dummy;

OUTPUT FOR CURRENT_TIME

11:35:05 PM

DAYNAME (d):

 This function returns the weekday in English for date d , which you need to pass it.

Example:

SELECT  DAYNAME ('2017-09-04') "DayName" FROM DUMMY;

Result:

 MONDAY

DAYOFMONTH (d):

This function returns an integer the day of the month for date d , which you need to pass it.

Example:

SELECT  DAYOFMONTH ('2017-09-04') "DayOfMonth" FROM DUMMY;

Result:

4

DAYOFYEAR (d):

This function returns an integer representation of the day of the year for date d, which you need to pass it.

Example:

SELECT  DAYOFYEAR ('2017-09-04') "DayOfYear" FROM DUMMY;

  

Result:

247

DAYS_BETWEEN (d1, d2):

It returns the number of days between two days d1 and d2

Example:

SELECT DAYS_BETWEEN (TO_DATE ('2017-09-04', 'YYYY-MM-DD'), TO_DATE('2017-09-24', 'YYYY-MM-DD')) "DaysBetween" FROM Dummy;

Result:

20

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d):

This function returns the value of a specified DateTime field from date d, like you can get a year, month, day, hour, minute and second.

Example:

SELECT  EXTRACT (YEAR FROM TO_DATE ('2017-01-04', 'YYYY-MM-DD')) "Extracted Value" FROM DUMMY;

  

Result:

2017