Jeff Moden (7/28/2014)
bpatin (7/28/2014)
This got me a few times in queries where I've used BETWEEN @FromDate AND @ToDate in the WHERE clause, and the data type may be either smalldatetime or datetime. If you don't calculate the end date correctly, you may miss anything on the last day of a quarter where the time is included, or may include the first day of the next quarter, if you don't calculate the end time correctly.
/* IMPORTANT: Uses 23:59 and NOT 23:59:59, because when compared with smalldatetime,
which is only accurate to the nearest minute, 23:59 will remain as 23:59,
but 23:59:59 will be rounded up to midnight on the first day of the the next quarter.
*/
SET @ToDate = CASE @Quarter
WHEN 1 THEN CONVERT(datetime,'3/31/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 2 THEN CONVERT(datetime,'6/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 3 THEN CONVERT(datetime,'9/30/'+CONVERT(varchar(4),@Year)+' 23:59')
WHEN 4 THEN CONVERT(datetime,'12/31/'+CONVERT(varchar(4),@Year)+' 23:59')
END
Shouldn't be done that way either because it doesn't catch the last second of the day and I DO have a lot happening then.
I strongly recommend what most folks consider to be the best practice of calculating (or pulling from a Calendar table) midnight of the next day and take everything before then. Then it truly doesn't matter what the date type is whether you use the low resolution of the DATE datatype or the high resolution of some forms the DATETIME2 datatype.
Similar to this (assuming the variables contain "whole" dates of one form or another)...
WHERE SomeDateColumn >= @StartDate
AND SomeDateColumn < DATEADD(dd,1,@EndDate)
Sorry... my previous example didn't match what your CASE statement did. Here's the code that would do the same thing and it would also allow for high-performance iTVFs because there would be no need to declare separate variables for a start or end date for the quarter. It also doesn't have to do any slightly slower character based conversions.
Note also that this example is based solely on calendar years. It would be a pretty simple matter to make it based on other fiscal years.
--===== Simulate the parameters of a stored procedure
DECLARE @pYear SMALLINT
,@pQuarter TINYINT
;
SELECT @pYear = 2014
,@pQuarter = 3
;
--===== Example of how to return data for the given quarter including the
-- calculations for the boundary dates of the quarter. No matter the
-- datatype, not even 100 nano-seconds would be lost.
SELECT SomeColumnList
FROM dbo.SomeTable
WHERE SomeDateColumn >= DATEADD(qq,@pQuarter-1,DATEADD(yy,@pYear-1900,0)) --Start of desired quarter.
AND SomeDateColumn < DATEADD(qq,@pQuarter ,DATEADD(yy,@pYear-1900,0)) --Start of quarter after that.
;
--Jeff Moden
Change is inevitable... Change for the better is not.