How can I select items from date interval regardless of years — Informix DB

user_10839776 0
Viewed 98

I have trouble with selecting items from a particular date interval. I want to select items from an interval based only on days and month.

items_table

 item_name  | date_from
item1         30.6.2015
item2         31.7.2015
item3         5.8.2019
item4         14.8.2000

I need something like this:

select * 
from items_table 
where date_from
between '****-07-31' and '****-08-13';

**** can be everything - for example 2010, 2011, 2012....

I would like to get only:

item2
item3
informix sql
2 Answers

You can use date parts with some arithmetic:

where month(date_from) * 100 + day(date_from) between 0731 and 0813

Or as strings:

where to_char(date_from, '%m-%d') between '0731' and '0813'
user_11440350

Another possible solution:

select * 
from items_table 
where
extend(date_from, month to day) between extend('2000-07-31'::date, month to day) and extend('1998-08-13'::date, month to day);

years 2000 and 1998 are irrelevant as they will be zeroed when comparison is performed.

user_9795900
Related