How do I do a comparison with just a month and year, not a complete date?

Question

I need to write a stored procedure to allow someone to search a db. However, all I get are ints for month and year. And the db has month and year fields. But I can't figure out how to set up the comparison.

Ex: I get March 2008 and June 2010.

I need to searhc the database for records where the date, as specified by the month and year fields, are between thoese two dates.

Edit

Given two Date inputs, how do I find all records that fall between those dates? Each record only has integers representing year and month.

Answer 1

Previous solutions should work, but unfortunately they prevent using index on year and month columns.

You will probaly have to wirte it the hard way:

SELECT  *
FROM    records
WHERE   Year > @StartYear
        OR ( Year = @StartYear
             AND Month >= @StartMonth)
/*(ommited end date check, it is same)*/

Another possibility is to add computed date column to source table

Storing date this way is suspicios and probably incorrect from DB design point of view. Consider storing date in date column and (if you really need it) adding computed columns for year and month.

Answer 2

Assuming you are provided Date variables called @StartDate and @EndDate in SQL Server:

SELECT
  *
FROM
  MyTable
WHERE  
  -- yields "200901 between 200801 and 201104" on inputs 01-01-2008, 04-01-2011
  Convert(VarChar(10), MyTable.Year) + Replace(Str(MyTable.Month, 2), ' ', '0')
   BETWEEN
  Convert(VarChar(10), YEAR(@StartDate)) + Replace(Str(MONTH(@StartDate), 2), ' ', '0')
   AND
  Convert(VarChar(10), YEAR(@EndDate)) + Replace(Str(MONTH(@EndDate), 2), ' ', '0')

References

• Simple Left-Padding for Int to String Conversion - Inspired Replace(Str(MyTable.Month, 2), ' ' , '0')
• TSQL Between