Sql: Comparing Dates With Only Month And Year Columns

I have a table MonthlyShipments that looks like: partnumber | quantity | month | year | part1 | 12 | 6 | 2011 | part1 | 22 | 5 | 2011 | part1 |

Solution 1:

Not much better...

DATEDIFF(
    month,
    DATEADD(Year, MonthlyShipments.Year-1900,
        DATEADD(Month, MonthlyShipments.Month-1, 0)
           ),
    GETDATE()
    ) BETWEEN 1 AND 3

however the nested DATEADD can be made a computed and indexed column

ALTERTABLE MonthlyShipments ADD
    ShipDate AS DATEADD(Year, MonthlyShipments.Year-1900,
            DATEADD(Month, MonthlyShipments.Month-1, 0)
               )

which gives

WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN1AND3

Solution 2:

Can you add a date column? If you need to do date calculations and you don't want things to get ugly this is probably going to be a requirement. Even a computed column would work...

Then you can just do something like this:

WHERE datecolumn < DATEADD(month, -3, GETDATE())

Solution 3:

You can convert your month+date to date and then compare it with current date:

WHERE DATEDIFF(m,CONVERT(DATE, [month]+'/01/'+[year],101), GETDATE()) 
BETWEEN1AND3

Or as it was said by others you can create a new computed column that holds date type

Solution 4:

If you have a (year,month) index, this will make use of it, I think:

FROM MonthlyShipments ms
WHERE ( ms.year = YEAR(GetDate()) 
        AND ms.month BETWEEN MONTH(GetDate())-3
                         AND MONTH(GetDate())-1
      )
   OR ( ms.year = YEAR(GetDate())-1
        AND ms.month BETWEEN 12 + MONTH(GetDate())-3
                         AND 12 + MONTH(GetDate())-1
      ) 

Not looking much prettier though.