Often times a date range is used in a where clause with a start date and an end date that are the same. The dates however are entered with the intention of getting that single day worth of data. By default T-SQL will look at that but not give you the full 24 hours worth of data. To fix that, toss in an IF statement early in the query to check and fix this problem. The code below is uber easy to just copy and paste into a SQL query for confirmation, then just alter for your custom needs.
DECLARE @EndDate datetime
DECLARE @StartDate datetime
SET @StartDate = ‘1/1/07’
SET @EndDate = ‘1/1/07’
PRINT ‘Start Date: ‘
PRINT @StartDate
PRINT ‘End Date: ‘
PRINT @EndDate
IF DATEPART(hh, @EndDate) = 0 BEGIN
SET @EndDate = DATEADD(hh, 23, @EndDate)
SET @EndDate = DATEADD(mi, 59, @EndDate)
SET @EndDate = DATEADD(ss, 59, @EndDate)
SET @EndDate = DATEADD(ms, 99, @EndDate)
END
PRINT ‘Start Date: ‘
PRINT @StartDate
PRINT ‘End Date: ‘
PRINT @EndDate
Ah yes, the whole combined date/time fields which are a pain in the arse… I can’t tell you how many times I’ve run into "off by 1 day" type errors in SQL because folks didn’t account for the time.
thanks for the GREAT post! Very useful…