Re: SQL Date function question

Brad,

I find it very hard to believe that Jan 1, 2011 is stored as
112011

What would Nov 1 2011 be stored as?
1112011

How about Jan 11 2011
1112011

See the problem? :)

My guess is the value stored is 01012011, but you see 1012011 when
using CAST or CHAR...

What you need is DIGITS

SUBSTR(DIGITS(datefld),5,4) as theYear.

HTH,
Charles


On Wed, Jul 6, 2011 at 4:51 PM, Bradley Stone <bvstone@xxxxxxxxx> 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?

Thanks!

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