The client has requested a low and high number of days as input parameters to a COBOL program. The program needs...
to then select a low and high date from sysibm.sysdummy1. I can get the following prototype SQL to work in SPUFI or QMF:
SELECT DATE(CURRENT DATE - (30) DAYS) , DATE(CURRENT-DATE - (90) DAYS) FROM SYSIBM.SYSDUMMY1
I've read the manuals and searched the DB2 listserv but have not been able to hit upon the correct COBOL host variable definition for the number of days for the following query to work:
SELECT DATE(CURRENT DATE - (:HV-LO-DAYS) DAYS) , DATE(CURRENT-DATE - (:HV-HI-DAYS) DAYS) INTO :HV-START-DATE , :HV-END-DATE FROM SYSIBM.SYSDUMMY1
We've tried defining the host variable as data type small integer host variable (i.e. PIC S9(4) USAGE COMP) and data type integer host value definition (i.e. PIC S9(9) USAGE COMP) which resulted in a -171 (data type, length, or value of argument nn of function-name is invalid) compile/bind error.
We've also tried the query with data type decimal 8,0 (i.e. S9(8) USAGE COMP-3) which allowed the program to compile and bind successfully but received a -310 (decimal host variable or parameter number contains non-decimal data) error when trying to execute the program. I know there's got to be a way to get this to work.
I haven't worked with dear old Completely Obsolete Business Oriented Language for many years. Please do not take offense, I loved that language, but it's like that bunny, still going, and going...
My guess is that DB2 is trying to return the dates as character strings. You will probably have to decide exactly how you want the date represented in COBOL. If you are targetting it into a S9(8) field, what sort of date format are you expecting that it should be a number? I suppose that if you wanted a Julian number as the result of the date calculation, then a numeric target field makes sense, but then the DATE() function is not going to give you that. If all you want is to display the start and end dates (on a web page, or printed on a report, say), then your target should probably be a character field and you should ask DB2 to make the conversion --
SELECT CHAR( DATE(CURRENT DATE - (:HV-LO-DAYS) DAYS) , ISO ) , CHAR( DATE(CURRENT-DATE - (:HV-HI-DAYS) DAYS) , ISO ) INTO :HV-START-DATE , :HV-END-DATE FROM SYSIBM.SYSDUMMY1
Note the DB2 CHAR() function converts dates to character strings according to the selected format -- ISO, UAS, EUR, or JIS.
For More Information
- What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.