Problem creating UDF for a specific date format

[jstrohofer]

Hi all,

First off, I am extremely new to DB2. Having said that, I have an application that uses a weird date format. Basically, it's a decimal of 6 or 7 numbers in length.

For instance, if the date were today, September 23, 2003, it would read 1030923. Basically if it is 7 numbers, it takes the first 3 and adds 1900 to it to get the year. Then the next two are the month, and the next two are the day.

Similarly, if the date were September 23, 1999, it would read 990923. Still the same arithmetic applies to get the year, and the month and day are both the two characters.

What I need to do is take a "normal" US date format such as 9/23/2003 and write a custom DB2 SQL Function to create the other date format.

Below is my code I am using, but it isn't working. I am actually calling this function using net.data and I am getting an Invalid Token error.

Any ideas are appreciated, and please excuse my ignorance on this topic as well.

Thanks,
Jill

(code is below)

CREATE FUNCTION UNITMFIL.PACKDT (date_input CHAR(10))
RETURNS DECIMAL(7,0)
LANGUAGE SQL
func1_lab:
BEGIN
DECLARE date_work CHAR(10);
DECLARE date_output DECIMAL(7,0);
DECLARE year_work CHAR(4);
DECLARE month_work CHAR(2);
DECLARE day_work CHAR(2);
DECLARE year_output CHAR(3);
SET date_work = DATE(TRIM(date_input));
SET year_work = CHAR(YEAR(date_work));
SET month_work = CHAR(MONTH(date_work));
SET day_work = CHAR(DAY(date_work));
IF LEN(month_work) = 1 THEN
month_work = "0" || month_work
END IF
IF LEN(day_work) = 1 THEN
day_work = "0" || day_work
END IF
IF LEFT(year_work, 2) = '19' THEN
SET year_output = SUBSTR(TRIM(year_work), 3, 2);
ELSE
SET year_output = '1' || SUBSTR(TRIM(year_work), 3, 2);
END IF;
SET date_output = DECIMAL(TRIM(year_output || month_work || day_work));
RETURN date_output;
END

 

[tcrisman]

jstrohofer,
I'm not super familiar with the language constraints of the function, but logically, you want to convert the input year to a number, and subtract 1900 from it. A rexx snippit, for example:
year=2003;outyear=year-1900;say year outyear;
results in:
2003 103
while
year=1950;outyear=year-1900;say year outyear;
results in:
1950 50
I believe you should be able to form the year part of your output with something like this:
DECLARE DYEAR DECIMAL(4.0);
SET DYEAR=(YEAR(date_work) - 1900);
SET YEAR_OUTPUT = CHAR(DYEAR);