Some of our reports were not showing items marked with a date and time when using the BETWEEN operator. This was because BETWEEN only considers values that are equal to one of the values, or falls in-between the range defined by them. When you use dates with with no time, the operator assumes the time is 0.
i.e.: BETWEEN '1/1/2010' and '1/2/2010' would mean '1/2/2010' would be treated as '1/2/2010 00:00:00.000'.
I created a date function to return a given day as the last second of the day. So '1/2/2010' will be returned as '1/2/2010 23:59:59.997. The milliseconds weren't really needed in my case, but I felt like doing it anyway.
Here is the function I created. I use a [DATE] schema for my date-related functions on our server.
/*
[DATE].[EndOfDay]
6/3/2010 JMJ
Returns a datetime set to the Last possible (milli) second of the day.
i.e. [DATE].[EndOfDay]('12/8/2008') would return '12/31/2008 23:59:59.997'
*/
CREATE FUNCTION [DATE].[EndOfDay] (
@InputDate DATETIME
)
RETURNS DATETIME
BEGIN
--Get Next day (with no time)
declare @dtNextDay datetime;
set @dtNextDay = DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, @InputDate), 0));
-- remove -2 milliseconds (that's as close as you can get)
-- which puts you into the last second of the original given day
return DATEADD(ms, -2, @dtNextDay);
END
----
Here is some example code using the function:
declare @dates table (
entrydate datetime
);
insert into @dates
select '1/1/2010 00:00:00.000'
union all
select '1/1/2010 10:00:00.000'
union all
select '1/2/2010 00:00:00.000'
union all
select '1/2/2010 12:00:00.000'
union all
select '1/2/2010 23:00:00.000'
;
select * from @dates
where entrydate between '1/1/2010' and '1/2/2010'
;
-- returns:
-- 2010-01-01 00:00:00.000
-- 2010-01-01 10:00:00.000
-- 2010-01-02 00:00:00.000
select * from @dates
where entrydate between '1/1/2010' and DATE.EndOfDay('1/2/2010')
;
-- returns:
-- 2010-01-01 00:00:00.000
-- 2010-01-01 10:00:00.000
-- 2010-01-02 00:00:00.000
-- 2010-01-02 12:00:00.000
-- 2010-01-02 23:00:00.000
