SQL (QMQUERY) date conversion issue

4055 pts.
Tags:
AS/400
SQL
I inherited a QMQUERY program that my user came to me today indicating that the week number was not correct. This query puts data into a workfile and a second query is called that splits other data into fields. As far as the two relevant fields are concerned, the second program does not modify them, just has them as part of the select statement. Pretty straight forward stuff. The relevant code in the select statement is this:
convdate(a.odudt) AsDueDate

week(convdate(a.odudt)) as Week
The input data shows a.odudt = 1160624. The result in the file shows AsDueDate = 2016-07-01 and week = 30. CONVDATE is a service program coded as follows (there is some testing for a valid date that I excluded, if the date is not valid it returns a null value.
========

CREATE FUNCTION qgpl/CONVDATE

(I_YMD DECIMAL(7,0))

RETURNS DATE

LANGUAGE SQL

BEGIN
DECLARE F_OUTPUT DATE;

SET F_OUTPUT = DATE(

SUBSTR(CHAR(19000000 + I_YMD),5,2) ||'/'||

SUBSTR(CHAR(19000000 + I_YMD),7,2) ||'/'||

SUBSTR(CHAR(19000000 + I_YMD),1,4)
);
Questions: why is this converting the date from 6/24 to 7/1? Why is the week coming up as 30? Note: this is a single example, but is reported to be frequent issue.
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • azohawk
    correction, I had the wrong record. the date is 7/1 in the file, but I still don't understand week 30, I come up with week 27 (based on what I understand of the week function.  (our calendar has 7/1 as week 26-but that is a seperate issue).
    4,055 pointsBadges:
    report
  • ToddN2000
    How old is this CONVDATE? It looks like there may be issues with the concatenation. I see 19000000 being used. Are they all old dates?
    135,525 pointsBadges:
    report
  • azohawk
    No, but they are stored in cyymmdd format, so adding the 19000000 makes sense. The date actually seems to be converting ok (at least they look ok in the file), it is when the week function is used sometimes we get strange information.
    4,055 pointsBadges:
    report
  • ToddN2000
    is there another function with the same name in a different library?
    The reason I am asking it the delimiters in the date are different between the concatenation and the result... / to -  
    135,525 pointsBadges:
    report
  • azohawk
    I did a wrkobj *all/convdate and got 1 hit.
    4,055 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: