× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have a *QRYDFN query that is working as expected.  I used RTVQMQRY to
convert it to SQL.  I prompted STRSQL and specified DATFMT(*ISO) DATSEP(-).
Note that TRNDT is 7 digits, the high order digit being the century digit;
i.e., 1040719 = 2004-07-19.  To verify that all 400,000+ records have a
valid TRNDT, I wrote a quickie program that does:

  c                   Eval      TranDate = %date( TrnDt : *cymd)
  c     *cymd         Move      TranDate      NewTrnDt
  c                   If        TrnDt <> NewTrnDt
  c                   Dump

(TranDate is a date data type, NewTrnDt is 7-digit numeric.)  This program
processed all 400,000+ records without any errors or dumps.

Also, PTTDATEFRM & PTTDATETO are both date data types and I have verified
them to be valid.

Regarding PTTDATEFRM & PTTDATETO, in writing this post, I used interactive
SQL (again, with DATFMT(*ISO) DATSEP(-)) to just SELECT * from
DJBTEST03/ARR101PTT, and the dates are shown in mm/dd/yy format.  Some of
the "TO" dates in this file are 9999-12-31 and they show up from this SQL as
++++++++.  So then I learn that DATFMT & DATSEP parameters were ignored
(SQL6335), so I used F13 to change the session attributes.  This did not
change the outcome.  Herewith is the SQL:

SELECT
  ALL       T01.PTTCOMNO, T01.PTTPAYTYPE, SUM(T02.AMTRC)
  FROM      DJBTEST03/ARR101PTT T01,  AMFLIB/ARHSTS T02
  WHERE     T01.PTTCOMNO = T02.COMNO
    AND     T01.PTTCHECK# = T02.CKNUM
    AND(    DATE(
             SUBSTR(DIGITS((TRNDT+19000000)),1,4)||'-'||
             SUBSTR(DIGITS((TRNDT+19000000)),5,2)||'-'||
             SUBSTR(DIGITS((TRNDT+19000000)),7,2) )
            BETWEEN PTTDATEFRM AND PTTDATETO )
    AND(    DATE(
             SUBSTR(DIGITS((TRNDT+19000000)),1,4)||'-'||
             SUBSTR(DIGITS((TRNDT+19000000)),5,2)||'-'||
             SUBSTR(DIGITS((TRNDT+19000000)),7,2) )
            BETWEEN '2004-06-01' AND '2004-06-30')
  GROUP BY  T01.PTTCOMNO, T01.PTTPAYTYPE
  ORDER BY  T01.PTTCOMNO ASC, T01.PTTPAYTYPE ASC

SQL0181: Value in date, time, or timestamp string not valid.

Suggestions & advice are wholeheartedly welcomed!

TIA,
db


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.