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
Wednesday, June 9, 2010
Thursday, March 18, 2010
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:
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.
Subscribe to:
Posts (Atom)