Skip to content
Advertisement

in rails i need to get all records based on a date range, ignoring the year

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement