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?

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.