Re: SQL Date function question

On 06-Jul-2011 13:51 , Bradley Stone wrote:

I've got a program that uses dynamic SQL and I'm having a hard time
finding documentation for this, or to find out if it's even
possible.

The file contains a date in the format MMDDCCYY. I need to compare
the year entered in a query with the year in the date. At first I
thought I could just substring off characters 5-8, but that won't
work since for example:

Jan 1, 2011

is stored as

112011

So obviously that won't work. :)

Does SQL on the iSeries have a way to convert this type of date to
something and then yank the year out to compare it against a text
literal?

Assumption: "date" refers to a column defined as CHAR(8)

Seems to be a data problem that should probably be corrected versus attempting to work-around; i.e. '112011' is not MMDDYYYY [or MMDDCCYY if preferred]. Nevertheless, given the full YYYY is truly always the rightmost four characters [digits]:

right( rtrim( char8field ) , 4 )

or perhaps if the four digits can safely be assumed:

dec( right( rtrim( char8field ) , 4 ) , 4 )

Regards, Chuck