SAP HANA Date Functions

Today is January 16, 2020. It’s been 12 years since SAP acquired BusinessObjects (see SAP press release, SAP and Business Objects Unite to Lead Emerging Market for Business Performance Optimization). In honor of the occasion, let’s use SAP HANA date functions to compute the number of years, months, days, seconds and milliseconds since the acquisition.

Let’s begin by determining the current date. Like Oracle, HANA has a dummy table. Unlike Oracle, SAP calls it “dummy” instead of “dual”.

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP,
CURRENT_UTCDATE, CURRENT_UTCTIME, CURRENT_UTCTIMESTAMP
FROM dummy

Using the TO_VARCHAR function, we can convert dates to strings.

-- Date Parts as Strings
SELECT TO_VARCHAR(CURRENT_DATE, 'YYYY') string_year,
TO_VARCHAR(CURRENT_DATE, 'YYYY-MM') string_month
FROM dummy

And lastly, it’s possible to use HANA’s date functions to determine the number of years, months, days, seconds, or even nanoseconds between two dates. Let’s see how much time has elapsed since SAP took control of BusinessObjects on January 16, 2008

-- Date Math
SELECT YEARS_BETWEEN(TO_DATE('2008-01-16'), CURRENT_DATE) my_age_in_years,
MONTHS_BETWEEN(TO_DATE('2008-01-16'), CURRENT_DATE) sap_bobj_in_months,
DAYS_BETWEEN(TO_DATE('2008-01-16'), CURRENT_DATE) sap_bobj_in_days,
SECONDS_BETWEEN(TO_DATE('2008-01-16'), CURRENT_TIMESTAMP) sap_bobj_in_seconds,
NANO100_BETWEEN(TO_DATE('2008-01-16'), CURRENT_TIMESTAMP) sap_bobj_in_ns
FROM dummy

Twelve years ago, the BusinessObjects partner summit was held in Las Vegas with the annual field sales kickoff meeting (FKOM), where SAP BusinessObjects Enterprise 3.0 was introduced with great fanfare (see related blog post, 2008 Business Objects Partner Summit). Xcelsius was a really big deal.

What were you doing twelve years ago?

Does Your Database Know Which Week It Is?

Where can you find the SAP BusinessObjects BI platform maintenance schedule calendar?

SAP maintains a release calendar for the SAP BusinessObjects BI platform, indicating by week when various support packs and patches are scheduled to arrive. For example, SAP BusinessObjects BI 4.2 SP8 is supposed to arrive in week 7 of 2020.

SAP BusinessObjects Maintenance Schedule

It will be the first release of BI 4.2 that does not sport new features and will only deliver bug fixes and additional platform support (browsers, databases, operating systems, etc.). If you haven’t patched in a while, BI 4.2 SP8 should be a safe place to hang out while you wait for SAP BI 4.3 (currently in beta) to become generally available (GA).

But which week is the current week of the year? Your database has functions that can help.

For Microsoft SQL Server:

SELECT datepart(week, getdate())

For Oracle:

SELECT TO_CHAR(SYSDATE, 'IW') FROM DUAL (ISO week)

OR

SELECT TO_CHAR(SYSDATE, 'WW') FROM DUAL (starts on January 1)

According to Wikipedia, ISO Weeks ‘IW’ “start with Monday. Each week’s year is the Gregorian year in which the Thursday falls.” So for 2020, the ISO week started on Monday, December 30, 2019 while the standard week ‘WW’ started on Wednesday, January 1, 2020.

For SAP HANA:

SELECT WEEK(CURRENT_DATE) FROM dummy

According to my databases, we are in week 3 of the year, so four more weeks to go before we can download the next patch release of the SAP BI platform.