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

Dan,

Try changing your substring offsets. The century/year in TRNDT is only 3
positions, not 4.

Steve Morrison
Beacon Insurance
Phone 940-720-4672
Fax   940-720-4345

-----Original Message-----
From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx] 
Sent: Monday, July 19, 2004 3:40 PM
To: Midrange Systems Technical Discussion
Subject: RE: How to chase down SQL0181 - Value in date, time, or timestamp
string not valid.

All right, starting to get somewhere.  Here is my latest test:

SELECT all  TrnDt,
                SUBSTR(DIGITS((TRNDT+19000000)),1,4)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),5,2)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),7,2)
         as TranDate
  FROM    DJBTEST03/ARHSTS$DB

The results:

Position to line  . . .
....+....1....+....2.
    TRNDT  TRANDATE
104/06/15  0200-40-61
104/06/30  0200-40-63
104/06/21  0200-40-62
********  End of data  ************

TRNDT is defined as 7 digits, 0 decimal, packed.

WHAT is going on here?  Is the DIGITS function coming up with a 9-digit
number from the (TRNDT+19000000) result?  Can I force the intermediate (?)
result to be 8 digits?

Can almost feel a solution...

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.