DATETIME FUNCTIONS

ADD_DAYS: This function is used to add N number of days to the existing date value.

Syntax: ADD_DAYS (date_value,N)
Example:  SELECT ADD_DAYS(‘2015-07-01’,30) AS “ADD DAYS” FROM DUMMY;
Res: ‘2015-07-31’ (Added 30 days to existing value)

SELECT ADD_DAYS(‘2015-07-01’,-30) AS “ADD DAYS” FROM DUMMY;
Res: ‘2015-06-01’ (subtracted 30 days from existing value.

ADD_MONTHS: This function is used to add N number of months to the existing date value.

Syntax: ADD_MONTHS (date_value,N)
Example:  SELECT ADD_MONTHS(‘2015-07-01’,1) AS “ADD MONTHS” FROM DUMMY;
Res: ‘2015-08-01’ (Added 1 month to the existing value)

SELECT ADD_MONTHS(‘2015-07-01’,-1) AS “ADD MONTHS” FROM DUMMY;
Res: ‘2015-06-01’ (subtracted 1 month from existing value)

ADD_SECONDS: This function is used to add N number of seconds to the existing time.

Syntax: ADD_SECONDS(time_value,N)
Example: SELECT ADD_SECONDS(‘2015-07-01 10:00:00’,60*30) AS “ADD SECONDS”FROM DUMMY;
Res: ‘2015-07-01 10:30:00.0’ (30 minutes added to the actual time)

SELECT ADD_SECONDS(‘2015-07-01 10:00:00’,-60*30) AS “ADD SECONDS” FROM DUMMY;
Res: ‘2015-07-01 09:30:00.0’ (30 minutes added to the actual time)

ADD_YEARS: This function is used to add N number of years to the existing date value.

Syntax: ADD_YEARS(date_value,N)
Example:  SELECT ADD_YEARS(‘2015-07-01’,1) AS “ADD YEARS” FROM DUMMY;
Res: ‘2016-07-01’ (Added 1 year to the existing value)

SELECT ADD_YEARS(‘2015-07-01’,-1) AS “ADD YEARS” FROM DUMMY;
Res: ‘2014-07-01’ (subtracted 1 year from existing value)

ADD_WORKDAYS: This function is used to compute a date by adding a number of workdays to a starting date.

Syntax: ADD_WORKDAYS (<factory_calendar_id>, <start_date>, <workdays> [, <source_schema>])
Note: Pre-requisite to use ADD_WORKDAYS and WORKDAYS_BETWEEN functions is the table TFACS (Factory calendar table should exist in HANA). If the system is SAP BW, SAP CRM and SAP ECC on HANA then the table will will available in ABAP schema which is SAP<SID>. If it is SAP HANA Plarform (stand alone) then table can be replicated using SLT or Data Services.

CURRENT_DATE: This function is used to return the current local system date.

Syntax: CURRENT_DATE
Example: SELECT CURRENT_DATE AS “CURRENT DATE” FROM DUMMY
Res: 2015-07-01 (Current system date. It is local date)

CURRENT_TIME: This function is used to return the current local system time.

Syntax: CURRENT_TIME
Example: SELECT CURRENT_TIME AS “CURRENT TIME” FROM DUMMY
Res: 16:48:03 (Current system time. It is local time)

CURRENT_TIMESTAMP: This function is used to return the current local system timestamp information.

Syntax: CURRENT_TIMESTAMP
Example: SELECT CURRENT_TIMESTAMP AS “CURRENT 

DATE&TIME” FROM DUMMY.

Res: 2015-07-01 16:48:03 (Local Current Date and Time)

CURRENT_UTCDATE: This function is used to return the current UTC date. (UTC – Coordinated Universal Time / Greenwich Mean Time (GMT)).

Syntax: CURRENT_UTCDATE
Example: SELECT CURRENT_UTCDATE AS “CURRENT UTC DATE” FROM DUMMY
Res: 2015-07-02 (Current UTC Date)

CURRENT_UTCTIME: This function is used to return the current UTC time.

Syntax: CURRENT_UTCTIME
Example: SELECT CURRENT_UTCTIME AS “CURRENT UTC TIME” FROM DUMMY
Res: 00:01:21 (Current UTC time)

CURRENT_UTCTIMESTAMP: This function is used to return the current local system timestamp information.

Syntax: CURRENT_UTCTIMESTAMP
Example: SELECT CURRENT_UTCTIMESTAMP AS “CURRENT UTC TIMESTAMP” FROMDUMMY
Res: 2015-07-02 00:01:21 (Current UTC Date and Time)

DAYNAME: This function is used to return the weekday in English for date d.

Syntax: DAYNAME (<d>)
Example: SELECT DAYNAME(‘2015-07-01’) AS “DAY NAME” FROM DUMMY
Res: WEDNESDAY (Day Name in English)

DAYOFMONTH: This function is used to return an integer for the day of the month for date d.

Syntax: DAYOFMONTH (<d>)
Example: SELECT DAYOFMONTH(‘2015-07-01’) AS “DAY OF MONTH” FROM DUMMY
Res: 1 (Day number in the month of given date)

DAYOFYEAR: This function is used to return an integer representation of the day of the year for date d.

Syntax: DAYOFYEAR (d)
Example: SELECT DAYOFYEAR(‘2015-07-01’) AS “DAY OF YEAR” FROM DUMMY
Res: 182 (Day number in the year of given date)

DAYS_BETWEEN: This function is used to compute the number of days between d1 and d2.

Syntax: DAYS_BETWEEN (d1, d2)
Example: SELECT DAYS_BETWEEN(‘2015-07-01′,’2015-07-15’) AS “DAYS BETWEEN”FROM DUMMY
Res: 14 (Difference between July 15th and July 1st)

EXTRACT: This function is used to find and return the value of a specified datetime field from date d.

Syntax: EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d)
Example: SELECT EXTRACT(YEAR FROM ‘2015-07-01’) AS “YEAR” FROM DUMMY
Res: 2015 (Year was extracted from the given date)

HOUR: This function is used to return an integer representation of the hour for time t.

Syntax: HOUR (t)
Example: SELECT HOUR(‘2015-07-01 16:45:12’) AS “HOUR” FROM DUMMY
Res: 16 (Hour has been extracted from the given time).

ISOWEEK: This function is used to return the ISO year and week numbers of date d. The week number is prefixed by the letter W.

Syntax: ISOWEEK (d)
Example: SELECT ISOWEEK(‘2015-07-01’) AS “ISO WEEK” FROM DUMMY;
Res: 2015-W27 (ISO week for along with the year for the given date)

LAST_DAY: This function is used to return the date of the last day of the month that contains the date d.

Syntax: LAST_DAY (d)
Example: SELECT LAST_DAY(‘2015-07-01’) AS “LAST DAY” FROM DUMMY;
Res: 2015-07-01 (last day of the month in the given date)

LOCALTOUTC: This function is used to convert the local time t from a timezone to the UTC(GMT) time

Syntax: LOCALTOUTC (t, timezone)
Example: SELECT LOCALTOUTC(‘2015-07-01 18:42:21′,’PST’) AS “LOCALTOUTC” FROMDUMMY;
Res: 2015-07-02 01:42:21 (GMT/UTC for the given PST time)

MINUTE: This function is used to return an integer representation of the minute for time t.

Syntax: MINUTE (t)
Example: SELECT MINUTE(‘2015-07-01 18:42:21’) AS “MINUTE” FROM DUMMY;
Res: 42 (Minute extracted from the given time)

MONTH: This function is used to return the number of the month from date d.

Syntax: MONTH(d)
Example: SELECT MONTH(‘2015-07-01’) AS “MONTH” FROM DUMMY;
Res: 7 (Month from the given date)

MONTHNAME: This function is used to return the name of the month in English for date d.

Syntax: MONTHNAME(d)
Example: SELECT MONTHNAME(‘2015-07-01’) AS “MONTH NAME” FROM DUMMY;
Res: JULY (Month name from the given date)

NANO100_BETWEEN: This function is used to compute the time difference between date arguments d1 and d2, to the precision of 0.1 microseconds.

Syntax: NANO100_BETWEEN (d1, d2)
Example: SELECT NANO100_BETWEEN (‘2015-07-01 18:40:11.000′,’2015-07-01 18:40:11.002’) AS “NANO BETWEEN” FROM DUMMY;
Res: 20000 (difference between given times in nano seconds)

NEXT_DAY: This function is used to return the date of the next day after date d.

Syntax: NEXT_DAY (d)
Example: SELECT NEXT_DAY(‘2015-07-01’) AS “NEXT DAY” FROM DUMMY;
Res: 2015-07-02 (Next day for the given date)

NOW: This function is used to returns the current timestamp.

Syntax: NOW ()
Example: SELECT NOW() AS “CURRENT TIME” FROM DUMMY;
Res: 2015-07-01 18:42:18.123 (Current time stamp (local system time))

QUARTER: This function is used to return the numerical year quarter of date d. The first quarter starts in the month specified by start_month. If start_month is not specified the first quarter is assumed to begin in January.

Syntax: QUARTER (d, [, start_month ])
Example: SELECT QUARTER(‘2015-07-01’) AS “QUARTER” FROM DUMMY;
Res: 2015-Q3 (Quarter from the given date by assuming starting month as January)

SELECT QUARTER(‘2015-07-01’,7) AS “QUARTER” FROM DUMMY;
Res: 2015-Q1 (Quarter from the given date by considering starting month for the first quarter as 7)

SECOND: This function is used to return a value of the seconds for a given time.

Syntax: SECOND (t)
Example: SELECT SECOND(’18:48:12′) AS “SECOND” FROM DUMMY;
Res: 12 (Second value from the given time)

SECONDS_BETWEEN: This function is used to compute the number of seconds between date arguments d1 and d2, which is semantically equal to d2 – d1.

Syntax: SECONDS_BETWEEN (d1, d2)
Example: SELECT SECONDS_BETWEEN(‘2015-07-01 12:00:00′,’2015-07-01 18:00:00’) AS“SECOND BETWEEN” FROM DUMMY;
Res: 21600 (seconds difference for the given two dates)

UTCTOLOCAL: This function is used to convert the UTC(GMT) time t to the local time in a time zone.

Syntax: UTCTOLOCAL (t, timezone)
Example: SELECT UTCTOLOCAL(‘2015-07-02 10:00:00′,’PST’) AS “UTC/GMT TO LOCAL”FROM DUMMY;
Res: 2015-07-02 03:00:00 (PST time for the UTC/GMT time)

WEEK: This function is used to return the week number of date d.

Syntax: WEEK (d)
Example: SELECT WEEK(‘2015-07-01’) AS “WEEK” FROM DUMMY;
Res: 27 (week number for the given date)

WEEKDAY: This function is used to return an integer representation of the day of the week for date d. The return value ranges from 0 to 6, representing Monday(0) through to Sunday(6).

Syntax: WEEKDAY (d)
Example: SELECT WEEKDAY(‘2015-07-01’) AS “WEEKDAY” FROM DUMMY;
Res: 2 (2 represents WEDNESDAY of the week from the given date)

WORKDAYS_BETWEEN: This function is used to compute the number of workdays between a <start_date> and an <end_date>.Computations are done with respect to a factory calendar with ID <factory_calendar_id>.

Syntax: WORKDAYS_BETWEEN (<factory_calendar_id>, <start_date>, <end_date> [, <source_schema>])
Example:
Note: Pre-requisite to use ADD_WORKDAYS and WORKDAYS_BETWEEN functions is the table TFACS (Factory calendar table should exist in HANA). If the system is SAP BW, SAP CRM and SAP ECC on HANA then the table will will available in ABAP schema which is SAP<SID>. If it is SAP HANA Plarform (stand alone) then table can be replicated using SLT or Data Services.

YEAR: This function is used to return the year number of date d.

Syntax: YEAR(d)
Example: SELECT YEAR(‘2015-07-01’) AS “YEAR” FROM DUMMY;
Res: 2015 (Year from the given date)