i am trying to get all records within a date range in rails that fall after a given start date and before a given end date, ignoring the year. the start date will just be a month. the end of the date range is a month and date. the following example is to get users with a hire date month equal to january or greater, but less than 2(february) / 28(day). i was trying this but it doesnt work:
users.where('extract(month from hire_date) >= ?', 1).where('extract(month from hire_date) <= ? AND extract(day from hire_date) <= ?', 2, 28)
the reason this doesnt work is it will exclude users, for example who fall on 1/29, because their day in january is not less than 28.
is there a good way to make this work, ignoring the year on the date field?
Advertisement
Answer
I would use to_char
to transform the dates into strings including just the month and the day. For example 2021-12-31
could be translated into "12-31"
. That string can then be compared to the range of date strings you are interested in.
users.where("to_char(hire_date, 'MM-DD') BETWEEN '01-01' AND '02-29'")
Note that this, in theory, this would also match invalid date strings like ’01-40′. But I guess it is safe to assume that to_char
with a valid date will never return such invalid date strings.