09 March 2010

Finding rows that intersect with a date period

This one is mainly a personal reminder but I hope it helps somebody else too. Let's say you have a table that covers something like currency exchange rates with columns for the start and end dates of the period each rate was applicable. Now you need to list the rates that applied during the year 2009. For some reason this always fazes me and I have to work it out with a diagram. So here's the recipe so I never have to do that again:

select  *

from    ExchangeRate

where   StartDate <= '31-DEC-2009'

        and EndDate >= '01-JAN-2009'

 

That is all!
Filed under:

Comments

# simonsabin said:

You need to make sure you get your equals signs correct.

I would suggest you need

select  *

from    ExchangeRate

where   StartDate <= '31-DEC-2009'

       and EndDate > '01-JAN-2009'

10 March 2010 16:11
# DavidWimbush said:

Damn, you're right. Thanks, Simon. In this case EndDate is the last day the row applies, so I think it should be <= and >=. Edited accordingly.

12 March 2010 12:53