SQL Server 2008 本月迄今

Question

希望这个问题很容易回答。

我正在处理一个需要 MTD 数据的表。 使用

MONTH (@monthtodate)= 11

我们的一位 SQL 人员告诉我在 SQL Server Management Studio 的参数列表中 Where @monthtodate is set to GetDate()。因此，他说，“理论上”，它应该选择月份 (11)，然后获取今天并返回这两个日期之间的所有请求的数据。但我认为这是不正确的。

在查看我的数据时，我开始认为它只是返回 11 月份整个月份的数据，而不仅仅是 MTD。我想，从技术上来说，任何有 0 的东西都不会被计算。但这仅仅意味着代码写得不好，对吗？

在您看来，这是返回 MTD 数据的更好方法吗：

production_date <= @today and Production_Date >= DATEADD(mm, DATEDIFF(mm, 0, @today), 0)

提前感谢大家！

Answer 1

我是这样做的。这应该适用于几乎任何版本的 SQL Server。

需要注意的一件重要事情是：从一开始，就应该始终建立一个代表“现在”（当前时刻）的单一值。如果您的查询中目前没有一致的值，那么当您的查询执行时，您最终会得到一些信息，以致它在运行时跨越日期边界。没有什么比向某人收取他们上个月已经支付的费用更好的了。最糟糕的是，像这样的边缘情况错误很难被开发人员或 QA 发现，因为两者都不太可能在 12 月 31 日 11:59 起作用。

代码：

declare
  @dtNow  datetime ,
  @Today  datetime ,
  @dtFrom datetime ,
  @dtThru datetime

---------------------------------------------------------------------------------------
-- set our effective notion of 'now'-ness.
--
-- We need have a consistent notion of now, lest we get bit in the a$
-- by an edge case where we cross a day/month/year boundary in mid-execution.
--
-- NOTE: Mostly, we're interested in the *DATE* rather than the actual moment-in-time.
--       So, we carry around two flavors here.
---------------------------------------------------------------------------------------
set @dtNow = current_timestamp
set @Today = convert(datetime,convert(varchar,@dtNow,112),112)

---------------------------------------------------------------------------------------
-- compute the current date.
--
-- 1. get the current date sans timestamp (effectively start-of-day)
-- 2. add 1 day, then back off 3 millseconds to set it to the last tick of the current day
--
-- NOTE: Depending on the requirements of your particular application (and the nature
--       of your data), you might want to use the actual current date/time value as
--       your upper bound.
--
-- FURTHER NOTE: How far to back off is dependent on your date/time type:
--
-- * For DateTime, the resolution is milliseconds and the last tick of the day
--   is 997 milliseconds, so you need to back off 3ms from the start of the
--   next day.
--
-- * SmallDateTime has a 1 second resolution. The last tick of the day, natch,
--   is 59 seconds, so you need to back off 1 second from the start of the next day.
--
-- * For DateTime2, the user declares the precision in decimal fractions of a second,
--   though its resolution is 100ns ticks. You'll need (especially if you're working
--   with DateTime2 columns/variables of differing precision) experiment to figure out
--   what traps Microsoft has set for you inside DateTime2 and what you need to do to
--   make things work properly.
--
---------------------------------------------------------------------------------------
set @dtThru = dateadd(ms,-3,dateadd(day,1,@Today))
--set @dtThru = @dtNow -- if you need the actual current date/time value

---------------------------------------------------------------------------------------
-- compute start of month
--
-- We do this by subtracting the day number of 'today' from the date/time value @today.
-- That gives us the last day of the prior month. Then we add one day to get the first
-- day of the current month.
---------------------------------------------------------------------------------------
set @dtFrom = dateadd(day,1-day(@Today),@Today)

---------------------------------------------------------------------------------------
-- finally, make your query for 'current month to date'
---------------------------------------------------------------------------------------
select *
from dbo.foobar t
where t.recorded_date between @dtFrom and @dtThru

Answer 2

如果您询问从性能角度来看这两个查询中哪一个更好：

DECLARE @now datetime = GETDATE()

SELECT * 
FROM yourTable 
WHERE Production_Date >= DATEADD(mm, DATEDIFF(mm, 0, @now), 0)
AND Production_Date < @now

SELECT * 
FROM yourTable 
WHERE YEAR(Production_Date) = YEAR(@now)
AND MONTH(Production_Date) = MONTH(@now)
AND Production_Date < @now

那么第一个就是，因为它会使用 Production_Date 上的索引（如果有）。但是，它们应该返回相同的结果。