# Thread: In need of Scalar Function example (dates and years)

1. Registered User
Join Date
Oct 2004
Location
Norway
Posts
53

## Unanswered: In need of Scalar Function example (dates and years)

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

2. Registered User
Join Date
Dec 2005
Posts
273
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)

3. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
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'```

4. Registered User
Join Date
Oct 2004
Location
Norway
Posts
53
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

5. Super Moderator
Join Date
Aug 2001
Location
UK
Posts
4,650
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•