I should start off by saying that I am a SQL Server guy, DB2 format is giving me a run for my $$.

I need to retreive records with the following date condition (suedo code below):

SELECT * FROM tablename WHERE
(issue_ts >= 1/1/current year AND< 9/1/current year)
AND
(
(expires_ts is null AND revoked_ts is null AND disabled_ts is null)
OR
(expires_ts > issue_ts + 1 day AND revoked_ts > issue_ts + 1 day AND disabled_ts > issue_ts + 1 day)
)

Any help would be much appreciated!

as a quick outline, follow these steps...

first, take DAYOFYEAR(CURRENT DATE) and subtract 1 from it, then subtract that number of days from the current date
this gives you january 1st of the current year

then just add 9 months to that for september 1st

and there you have your date range boundaries

Perfect, this gets me started.....

select
CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY AS JAN,
CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY + 8 MONTHS AS SEPT
FROM sysibm.sysdummy1

Why does

where DATE(SUB2.ISSUED_ON_TS) >= CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY
AND DATE(SUB2.ISSUED_ON_TS) < CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) + 8 MONTHS

give me the following error?

"SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF - IS INVALID"


Yet,

select
CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY AS JAN,
CURRENT DATE - ( DAYOFYEAR(CURRENT DATE) - 1 ) DAY + 8 MONTHS AS SEPT
FROM sysibm.sysdummy1

works fine....?

quite right, it's 8 months from jan 1 to sept 1

are you sure you can say + 8 MONTHS?

shouldn't it be + 8 MONTH?

in any case, i think you have to repeat the DAY...

Damn typos...., adding DAY fixed the error. As for MONTH vs MONTHS... I have no idea, both produce the same results. I will use MONTH unless someone says otherwise. Thanks very much for your help!