SQL:只月份和年份列比较日期
问题描述:
我有一个表MonthlyShipments
看起来像:SQL:只月份和年份列比较日期
partnumber | quantity | month | year |
part1 | 12 | 6 | 2011 |
part1 | 22 | 5 | 2011 |
part1 | 32 | 4 | 2011 |
part1 | 42 | 3 | 2011 |
part1 | 52 | 2 | 2011 |
我要总结的近3个月的数量,不包括当前月份。我的where条款目前看起来像:
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())
它的作品,但它的丑陋和侮辱。任何建议使它更漂亮?非常感谢!
答
也好不到哪去...
DATEDIFF(
month,
DATEADD(Year, MonthlyShipments.Year-1900,
DATEADD(Month, MonthlyShipments.Month-1, 0)
),
GETDATE()
) BETWEEN 1 AND 3
但是嵌套DATEADD可以由计算和索引列
ALTER TABLE MonthlyShipments ADD
ShipDate AS DATEADD(Year, MonthlyShipments.Year-1900,
DATEADD(Month, MonthlyShipments.Month-1, 0)
)
这给
WHERE DATEDIFF(month, ShipDate, GETDATE()) BETWEEN 1 AND 3
答
你可以添加日期柱?如果你需要做日期计算,并且你不想让事情变得丑陋,这可能是一个需求。即使是computed column会工作......
然后,你可以做这样的事情:
WHERE datecolumn < DATEADD(month, -3, GETDATE())
答
您可以在一个月+日期转换为date
然后将其与当前日期比较:
WHERE DATEDIFF(m,CONVERT(DATE, [month]+'/01/'+[year],101), GETDATE())
BETWEEN 1 AND 3
或者正如其他人所说,您可以创建一个新的计算列,其中包含date
类型
答
如果您有(year,month)
指数,这将使用它,我认为:
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
)
虽然看起来不漂亮多了。
这是丑陋的... – 2011-06-08 18:53:59