Migrating from TSQL: The DATEADD function
Comments (5) Visits (22459)
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.
This TSQL is a bit of an odd one.
It takes three arguments:
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 MSSQ
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');
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);
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)