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

[CWilt@xxxxxxxxxxxx]

Dan,

You could, by using the cast function.

substr(digits(cast(TRNDT+190000000) as dec(8,0)),1,4)

But notice that for the month and year, you could also simply drop the
+190000000.

substr(digits(cast(TRNDT+190000000) as dec(8,0)),1,4)
||'-'||substr(digits(TRNDT),4,2)
||'-'||substr(digits(TRNDT),6,2)


All in all, a good example of why you should use a UDF once you get it
working ;-)

Charles


> -----Original Message-----
> From: Dan Bale [mailto:dbale@xxxxxxxxxxxxx]
> Sent: Monday, July 19, 2004 4: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.
>