Re: Bad numeric data

You could also wrap the case statement in a ifnull((case ... ), '0001-01-01')


Sent from my iPad

> On May 14, 2015, at 1:39 PM, Darryl Freinkel <dhfreinkel@xxxxxxxxx> wrote:
> 
> 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.
>>