RE: How to chase down SQL0181 - Value in date, time, or timestamp string not valid.

Dan,

Try specifying the date concatenation as a result field.  This has been very 
helpful to me in determining exactly how SQL is interpreting dates I build.  
You could leave out everything except the date concatenation to test out your 
build.

I got tired of having to go through what you are doing so I wrote a UDF to 
convert the different date data types into an ISO format.  Now I use it to 
convert the dates for comparisons.  Much better.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Dan Bale
Sent: Monday, July 19, 2004 1:13 PM
To: midrange-l@xxxxxxxxxxxx
Subject: How to chase down SQL0181 - Value in date, time,or timestamp
string not valid.


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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.