Search code examples
sql-servert-sqldaterangeperiod

Need help with a SQL query selecting date ranges from a table of period quarters

I have a table called Periods that looks like this

PeriodID | PeriodYear | PeriodQuarter

7 | 2009 | 1

8 | 2009 | 2

9 | 2009 | 3

10 | 2009 | 4

11 | 2010 | 1

12 | 2010 | 2

Each row in the table represents 1 of the 4 quarters of the year (like 3-monthly school terms). E.g. The first row represents Period 1 of 2009 (i.e. the date range 1 Jan 2009 - 31 March 2009.

Now I need to write a query that selects rows/periods from the above table, where the period occurs between 2 date ranges, as per the following pseudocode.

select *
from Periods
where Period is between @startDate and @endDate

The query will be used inside a table-valued function called dbo.GetPeriodsFromDateRange, and @startDate and @endDate are parameters to the function.

I'm stuck and can't figure out how to do it. Please help. This applies to T-SQL (MS SQL Server 2000/2005)

Solution

  • Try

    select *
from Periods
where  dateadd(qq,PeriodQuarter-1,dateadd(yy,PeriodYear -1900,0)) 
between @startDate and @endDate