SQL: Comparing Dates with only Month and Year colu

问题:

I have a table MonthlyShipments that looks like:

partnumber | quantity | month | year |
part1      |       12 |     6 | 2011 |
part1      |       22 |     5 | 2011 |
part1      |       32 |     4 | 2011 |
part1      |       42 |     3 | 2011 |
part1      |       52 |     2 | 2011 |

I want to sum the quantities of the past 3 months, excluding the current month. My where clause currently looks like:

where
  MonthlyShipments.Month <> MONTH(GETDATE()) AND 
  CAST(
    (CAST(MonthlyShipments.Month as nvarchar(2)) + 
    '-01-' + 
    CAST(MonthlyShipments.Year as nvarchar(4))) as DateTime)
  > DATEADD(m, -4, GETDATE())

It works, but its ugly and insulting. Any advice on making it prettier? Much appreciate!

回答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

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

which gives

WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN 1 AND 3

回答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())

回答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()) 
BETWEEN 1 AND 3

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

回答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.