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

["Dan Bale" <dbale@xxxxxxxxxxxxx>]

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx / Rick.Chevalier@xxxxxxxxxxxxxxx
> Sent: Monday, July 19, 2004 2:23 PM
>
> 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, not sure what you were suggesting for a result field; here's what I
did:

SELECT all DATE(SUBSTR(DIGITS((TRNDT+19000000)),1,4)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),5,2)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),7,2))
         as TranDate
  FROM    AMFLIB/ARHSTS
  WHERE   TranDate < date('2004-06-01')

Result: SQL0206 "Column TRANDATE not in specified tables."

TranDate does not exist in the file; I intended it to be an SQL-defined
variable.  Again, I am failing to find useful information in the SQL
reference.  I'm sure I have seen the AS clause before.

Is your date UDF sharable?

Thanks again Rick,
db