× 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.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


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.