In need of Scalar Function example (dates and years)

Filip Poverud · #1 (**permalink**) 04-04-06, 04:48

I'm trying to write a generic filter condition so I can run a query any time and the returned data is between this years last month and 12 months back.

What I've achieved so far is to get data from 12 months back, based upon the current date.

where
mytimestampcol between
current_date - 12 months and current_date - 1 months;

---

I've also tried the mathemathical approach with month() and year() functions, although this gives me data from 01.01.05 to 31.03.06 whereas I wanted 01.04.05 to 31.03.06 (if I ran the query today)

---

Thank you in advance

with Regards
Filip Poverud

umayer · #2 (**permalink**) 04-04-06, 05:12

for Db2 for z/OS this should work:

... BETWEEN
ADD_MONTHS(LAST_DAY(CURRENT DATE),-13) + 1 DAY
AND
ADD_MONTHS(LAST_DAY(CURRENT DATE),-1)

Marcus_A · #3 (**permalink**) 04-04-06, 05:27

Code:

select * from emp
where hiredate >=
cast(year(current_date -12 months)as char(4)) || '-' ||
cast(cast(month(current_date - 12 months) as decimal(2,0) ) as char(2)) || '-' || '01'  
and  hiredate < 
cast(year(current_date)as char(4)) || '-' ||
cast(cast(month(current_date) as decimal(2,0) ) as char(2)) || '-' || '01'

Filip Poverud · #4 (**permalink**) 04-04-06, 07:07

umayer: thx, I haven't found last_day() on LUW though but its working like a charm on z/OS

Marcus: thx, although it doesn't look good (my objective opinion) it's working and thats what I wanted to achieve

I had to wrap the predicate with date() as it is of type timestamp.

with Regards
Filip Poverud

sathyaram_s · #5 (**permalink**) 04-04-06, 11:08

Filip,

See if you 'like' this

select * from syscat.tables where date(create_time) between
(current date - (day(current date)) days + 1 day - 12 months) and
(current date - day(current date) days)

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech