End Of Day(s) Function

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

This blog has moved (kind of)

This blog is now located at http://blog.jjerome.com/.
You will be automatically redirected in 30 seconds, or you may click here.

For feed subscribers, please update your feed subscriptions to
http://blog.jjerome.com/feeds/posts/default.

A few more things to note:

• I renamed it from "projects" to "blog" in a few places. I figured the term blog as a little more general.
• I host my blog via Blogger thinking that it would be a little easier to manage than one I hosted myself (time, security updates, etc). However, they got rid of FTP support so I was forced to make these changes. All old links (jjerome.com/projects) automatically point to the new location (blog.jjerome.com).
  
• Somewhere along the line Blogger lost some images I had included within a few posts (screen shots, etc). I have no idea how it lost them, but I'll have to look around to see if I can find them and update the original posts. In most cases the instructions I give are enough to not really need the screen shots.