Re: SQL Date function question

Try the MOD function, as in MOD(datefld,10000). Less "messy" I think than what you have below and probably just as efficient.

This code for example, seems to do what is needed:

with xxx as (
select decimal(01012011,8,0) dte1,
decimal(01112011,8,0) dte2
from sysibm/sysdummy1)
select dte1, mod(dte1,10000) yr1,
dte2, mod(dte2,10000) yr2,
hex(dte1) hex1, hex(dte2) hex2
from xxx

Run, it give me this, which will probably
....+....1....+....2....+....3....+....4....+....
DTE1 YR1 DTE2 YR2
1,012,011 2,011 1,112,011 2,011

5....+....6....+....7....
HEX1 HEX2
001012011F 001112011F

HEX is a trick used simply to see exactly what is in the field in question. You said that it shows up correctly in SQL, therefore it must be valid packed data, and HEX can be used to confirm exactly what is in there.

Sam

On 7/6/2011 7:16 PM, Bradley Stone wrote:

(((substr(char(CDATE),5,4)>= '2010') and (substr(char(CDATE),5,4)<=
'2010')) or ((substr(char(CDATE),4,4)>= '2010') and
(substr(char(CDATE),4,4)<= '2010')))

Messy, but it appears to work.. lol...