sql - SQL Server 2008 中的本月至今

Question

希望这将是一个容易回答的问题。

我正在处理需要 MTD 数据的表。我们的一位 SQL 人员告诉我使用

MONTH (@monthtodate)= 11

哪里 @monthtodate设置为 GetDate()在 SQL Server Management Studio 的参数列表中。因此，在“理论上”，他说，它应该选择月份 (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