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.

Easily done, but for which db flavor? – Paul Sasik Aug 27 '11 at 03:22 — Paul Sasik, Aug 27 '11 at 03:22

score 5 · Answer 1 · edited Aug 27 '11 at 10:46

5

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.

edited Aug 27 '11 at 10:46

Martin Smith

438,706
87
741
845

answered Aug 27 '11 at 10:43

MaD

96
1

The year and month are hopefully result of a subquery that we have been spared from, maybe as a computed column, I honestly hope those year and month columns are not indexed – t-clausen.dk Aug 27 '11 at 12:51

score 1 · Accepted Answer · edited May 23 '17 at 12:04

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

score 0 · Answer 3 · answered Aug 27 '11 at 03:37

0

An unconventional approach:

select * from sometable
    where YourYear * 100 + YourMonth 
    between @SomeStartYear * 100 + @SomeStartMonth and @SomeendYear * 100 + @SomeEndMonth

answered Aug 27 '11 at 03:37

aquinas

23,318
5
58
81

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

3 Answers3

Linked