Re: Bad numeric data

This is how I do the date conversions to date fields.

Case when month between 1 and 12 then date(substr(digits(date_field),1,4) || '-' || substr(digits(date_field),5,2) || '-' || substr(digits(date_field),7,2) )
Else date('2012-12-12')
End

I use the 2012-12-12 to identify records with bad dates. Normally, that does not happen. You could use any date value to identify and error date value.


Sent from my iPad

> On May 14, 2015, at 11:42 AM, John McKee <jmmckee3@xxxxxxxxx> wrote:
> 
> A field on a file on the remote 520 is defined as numeric 8 0.  Supposed to
> have date in form of CCYYMMDD.  I ran a query on the field.  Lots of bad
> values.  I was hoping to see what might be in the field.  Never worked with
> CAST in SQL.    Never needed to before.
> 
> When I try this:
> 
> select cast(xxxxxx as char(20) from xxxxxx. I get either some numeric value
> (usually a single 0) or the '+' sign with it repeated 20 times.
> 
> An IBM query on the field showed the values as different - at least when
> sorted and grouped, each group had one record.
> 
> I am not sure, yet, what value should be forced into the field to clean up
> the data.
> 
> How do I add a where to the above select to only locate non-numeric values?
> 
> John McKee
> -- 
> 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.
>