Db2

 View Only
Back to Library

Migrating from TSQL: The DATEADD function

Mon March 09, 2020 05:25 PM

Posted by: Serge Rielau
Background

When our team added compatibility for DB2 with Oracle applications we learned that no two functions were compatible between Oracle and DB2 as far as date-time arithmetic was concerned.
We ended up adding an entirely new library to achieve compatibility.
It appears the difference between DB2 and MS SQL Server is equally profound in this area given that I was recently asked to provide a match for the DATEADD() function.

DATEADD()

This TSQL is a bit of an odd one.
It takes three arguments:
  1. A "measure"
    The measure can be anything from a year to a nanosecond including weeks, weekdays and a variety of shorthands and synonyms fro these.
  2. A 'delta"
    The delta is a 32bit integer value of that measure which denotes how many units of the measure are to be added or subtracted.
  3. A "date"
    This can be either a timestamp or a date to which the delta measures are to be added.
If the "date" is a datetime (TIMESTAMP(0) then rounding can occur in so far that split seconds are rounded up or down to the next full second.
That part is fine.
The problem arises with the fact that the "measure" argument is not a string. It is one of many keywords.
So instead of
DATEADD('HOUR', 3, CURRENT DATE)
you must specify
DATEADD(HOUR, 3, CURRENT DATE)  
 
While it's easy enough to provide a UDF in DB2 which matched DATEADDs function in DB2 handling the keywords requires a small amount a trickery.
It is this trickery which I find justifies a blog post..

The DB2 solution

For simplicity I will concentrate on a generic DATEADD() function for TIMESTAMP(9).
If you want to mimic the datetime (TIMESTAMP(0)) I do recommend a DISTINCT type.
That will allow for overloading of DATEADD() and thus proper implementation of the rounding semantics.
CREATE OR REPLACE FUNCTION MSSQL.DATEADD(datepart VARCHAR(20),
                                         number   INTEGER,
                                         date     TIMESTAMP(9))
  RETURNS TIMESTAMP(9) 
  NO EXTERNAL ACTION DETERMINISTIC CONTAINS SQL
  RETURN CASE datepart WHEN 'year'        THEN date + number YEARS
                       WHEN 'quarter'     THEN date + (number * 3) MONTHS
                       WHEN 'month'       THEN date + number MONTHS
                       WHEN 'week'        THEN date + (number * 7) DAYS
                       WHEN 'day'         THEN date + number DAYS
                       WHEN 'hour'        THEN date + number HOURS
                       WHEN 'minute'      THEN date + number MINUTES
                       WHEN 'second'      THEN date + number SECONDS
                       WHEN 'millisecond' THEN date + (number * 1000) MICROSECONDS
                       WHEN 'microsecond' THEN date + number MICROSECONDS
                       WHEN 'nanosecond'  THEN date + BIGINT(number / 1000.0) MICROSECONDS
                       ELSE RAISE_ERROR('78000', 'Unknown datepart: ' || datepart) END;

VALUES MSSQL.DATEADD('year', 2, CURRENT TIMESTAMP);
1
-----------------------------
2014-10-14-19.31.45.328000000

  1 record(s) selected.
We have the right functionality, but how do we get rid of the single quotes?
If the function is invoked from an application adding the quotes can get quite ugly.
Luckily DB2 introduced "global variables" in DB2 9.5 (I think it was 9.5 - so long ago).
So what we can do is define global constants which act as keywords to our function: 
CREATE OR REPLACE VARIABLE MSSQL.YEAR        VARCHAR(20) CONSTANT ('year');
CREATE OR REPLACE VARIBALE MSSQL.QUARTER     VARCHAR(20) CONSTANT ('quarter);
CREATE OR REPLACE VARIABLE MSSQL.MONTH       VARCHAR(20) CONSTANT ('month');
CREATE OR REPLACE VARIABLE MSSQL.WEEK        VARCHAR(20) CONSTANT ('week');
CREATE OR REPLACE VARIABLE MSSQL.DAY         VARCHAR(20) CONSTANT ('day');
CREATE OR REPLACE VARIABLE MSSQL.DAYOFYEAR   VARCHAR(20) CONSTANT ('day');
CREATE OR REPLACE VARIABLE MSSQL.WEEKDAY     VARCHAR920) CONSTANT ('day');
CREATE OR REPLACE VARIABLE MSSQL.HOUR        VARCHAR(20) CONSTANT ('hour');
CREATE OR REPLACE VARIABLE MSSQL.MINUTE      VARCHAR(20) CONSTANT ('minute');
CREATE OR REPLACE VARIABLE MSSQL.SECOND      VARCHAR(20) CONSTANT ('second');
CREATE OR REPLACE VARIABLE MSSQL.MILLISECOND VARCHAR(20) CONSTANT ('millisecond');
CREATE OR REPLACE VARIABLE MSSQL.MICROSECOND VARCHAR(20) CONSTANT ('microsecond');
CREATE OR REPLACE VARIABLE MSSQL.NANOSECOND  VARCHAR(20) CONSTANT ('nanosecond');

VALUES MSSQL.DATEADD(MSSQL.DAY, 5, CURRENT DATE);
1
-----------------------------
2012-10-19-00.00.00.000000000

  1 record(s) selected.
We are getting closer.
TSQL supports various acronyms like NS for NANOSECOND etc.
But It's easy enough to add these, as well as to add WEEKS and other measures to the function itself.

Now all that's left is to get rid of the schema name "MSSQL".
Both functions and variables obey the PATH special register.
Adding MSSQL to PATH will do the trick.

Of course you can also place this logic into the CONNECT_PROC procedure as described frequently in other posts in this blog.
SET PATH = CURRENT PATH, MSSQL;

VALUES DATEADD(hour, 10, CURRENT TIMESTAMP);
1
-----------------------------
2012-10-15-05.40.08.250000000

  1 record(s) selected.

Conclusion

It is often fairly easy to extend DB2 to match functions from an other SQL dialect.
In this case the use of keywords in TSQL can be compensated easily through the usage of global constants.
What remains to be said, for completeness, is that these constants can of course be used anywhere in the database where variable are allowed.
They obey the rules for scoping. So beware of using column names which match these keywords in conjunction with the DATEADD() function:
SELECT CURRENT TIMESTAMP, DATEADD(HOUR, 3, CURRENT TIMESTAMP) 
  FROM (VALUES('minute')) AS T(HOUR);
1                          2
-------------------------- -----------------------------
2012-10-14-19.50.07.281000 2012-10-14-19.53.07.281000000

  1 record(s) selected.

#Db2

Statistics
0 Favorited
1 Views
0 Files
0 Shares
0 Downloads

Related Entries and Links

No Related Resource entered.