DavidWimbush

Observations of a SQL Server DBA and Reporting & BI developer.

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!
Posted: Mar 09 2010, 07:40 PM by DavidWimbush | with 2 comment(s)
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'

# March 10, 2010 4:11 PM

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.

# March 12, 2010 12:53 PM