iSeries DB2 SQL - Update Variable in UDF Using CASE Statement

Question

I am attempting to create a user defined function for DB2 and cannot figure it out. I am much more comfortable with t-sql, which would do this in about a dozen ways. I need help as all I get in return are cryptic error messages that tell me there are no valid tokens or some such.

The basic premise is that I am trying to convert a normal date into a date format used throughout the database (cyymmdd) Here goes the pseudo-code:

Get current time;
If YEAR(current time) > 1999 then 1 else 0;

Simple enough, right? Can't get it to work. Here is the actual code:

DECLARE Cn CHAR ( 1 ) ;
CASE
WHEN YEAR(X) > 1999
THEN SET Cn = 1;
ELSE SET Cn = 0;

Also tried:

DECLARE Cn CHAR ( 1 ) ;
CASE YEAR(X)
WHEN  > 1999
THEN SET Cn = 1;
ELSE SET Cn = 0;

And that fails miserably as it should.

I am really at a loss as to how I can convert 3/25/2014 into 1140325 with just SQL. I guess I can handle this in application logic, but that isn't the way I want to go with this.

Thanks in Advance.

Answer 1

It's a simple calculation to convert a DATE to CYMD:

SELECT (YEAR(CURRENT_DATE) - 1900) * 10000 
  + MONTH(CURRENT_DATE) * 100 
  + DAY(CURRENT_DATE)
FROM SYSIBM.SYSDUMMY1;

---

A UDF to perform the conversion:

CREATE FUNCTION QGPL.DATE2CYMD(DATE DATE) RETURNS DEC(7)
LANGUAGE SQL
DETERMINISTIC
BEGIN
    RETURN (YEAR(DATE) - 1900) * 10000
    + MONTH(DATE) * 100
    + DAY(DATE);
END

A test to verify it's working correctly:

SELECT 
    QGPL.DATE2CYMD(CURRENT_DATE) CURRENT_CYMD, 
    QGPL.DATE2CYMD(CAST(NULL AS DATE)) NULL_CYMD
FROM SYSIBM.SYSDUMMY1;