Month to Date in SQL Server 2008

Question

Hopefully this will be an easy one to answer.

I am working on a table that requires MTD data. One of our SQL guys told me to use

MONTH (@monthtodate)= 11

Where @monthtodate is set to GetDate() in the parameter list in SQL Server Management Studio. So in "theory", he says, it should select the month (11) and then get today and return all the requested data in between those two dates. But I'm thinking this isn't correct.

In looking at my data I'm starting to think that It's just returning data for the whole month of November instead of just MTD. I guess, technically, anything that has 0 won't be calculated. However that just means it's poorly written code correct?

In your opinions, would this be the better way to return MTD data:

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

Thanks in advance everyone!

Solution

Here's how I do it. This should work on pretty much any version of SQL Server.

One important thing to note: at the outset, one should always establish a single value that represents 'now', the current moment in time. If you do not have a consistent value for now in your query, you will eventually get bit when your query is executed such that it crosses a date boundary whilst in-flight. Nothing like billing somebody for something they already paid for last month. Worst, edge-case bugs like that are difficult to catch, either by developers or by QA, since neither is likely to be working, say, at 11:59 on December 31.

The code:

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

Comments

[Aaron Smith]

Thanks Nicholas! I KNEW that seemed odd but given my minimal knowledge of basic SQL, time/date is a pain! This is the BEST MTD answer I've found! +1 to you! I'll be sharing this in my DB class for sure.

[SQLMenace]

Be careful with between, better to do < dateadd(day,1,@Today) because SQL Server 2008 also has datetime2 which goes to 100th nanosecond precision, in that case you will be leaving out rows

[Nicholas Carey]

@SQLMenace: x between y and z is exactly identical to ` x >= y and x <= z. No difference regardless of datatype. You'll also notice that I'm using DateTime here not DateTime2: If I was using Datetime2, I'd be adjusting the upper bound to the appropriate 100ns bound. Between` makes for much cleaner and more readable code. But thanks for the suggestion.

[SQLMenace]

even though you are using datetime what if someone changes the data type of the table to datetime2..your code is still running but you are missing rows and you won't even know it >= and < is always safe

[Nicholas Carey]

That sort of schema change requires a code review to see its effects on existing stuff, especially given all the interesting pitfalls regarding boundary conditions implicit in the older date/time types.