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

Dan,

Try this
SELECT DATE(SUBSTR(DIGITS((TRNDT+19000000)),1,4)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),5,2)
        ||'-'|| SUBSTR(DIGITS((TRNDT+19000000)),7,2))
         as TranDate
  FROM    AMFLIB/ARHSTS
  
All it will do is show the date values but at least you will know if they are 
correct.

I think the UDF would be shareable.  It's just a series of date operations 
surrounded by monitor groups.  What release are you at?

Rick


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


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

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