SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BETWEEN date1 AND date2 Not returning data for date2


BETWEEN date1 AND date2 Not returning data for date2

Author
Message
Michael D Slifer
Michael D Slifer
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 24
I'm using a WHERE moddate BETWEEN '06/01/2008' AND '06/30/2008' clause to extract detail records from a table. The moddate field is a datetime field. All records are returned except for the last date. If I expand the date range to '07/01/2008' the '06/30/2008' data is extracted.
I've tried WHERE CONVERT(char (10), moddate, 101) with the same results. What am I missing?
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78826 Visits: 14970
DateTime fields store date and Time so if you are using
Between '6/1/08' and '6/30/08'

then you are actually saying give me all the data From 6/1/08 00:00:00 to 6/30/08 00:00:00 so you will not get results for 6/30/08 00:00:01 and later. This is why most people recommend using
 date >= '6/1/08' and date < '7/1/08'

instead of between for date ranges. Now in SQL Server 2008 with the new Date datatype your between would do what you think it would.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
george sibbald
george sibbald
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44228 Visits: 13705
if you do not specify the time it defaults to 00:00:00 hence you won't get 06\30 entries. include the time in the range.

-- or what jack said

---------------------------------------------------------------------
Michael D Slifer
Michael D Slifer
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 24
Thanks to both of you, I knew it was something simple.
Orion Pax
Orion Pax
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 350
I've just added this for reference purposes.

-- declare temprary table to store dates
DECLARE @tblDateTime TABLE ([DateTime] DATETIME);

-- populate temporary dates to be used for reference purposes
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/01 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/02 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/03 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/04 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/05 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/06 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/07 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/08 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/09 15:00:00');
INSERT INTO @tblDateTime ([DateTime]) VALUES ('2008/01/10 15:00:00');

-- the following will not return the last date (2008/01/10')
SELECT *
FROM @tblDateTime
WHERE
[DateTime] BETWEEN '2008/01/01' AND '2008/01/10'

-- the following will return the last date (2008/01/10'). second being the recommended version as it has a performance benifit over convert.
SELECT *
FROM @tblDateTime
WHERE
CONVERT(VARCHAR(max), [DateTime], 111) BETWEEN '2008/01/01' AND '2008/01/10'

SELECT *
FROM @tblDateTime
WHERE
DATEADD(dd, DATEDIFF(dd, 0, [DateTime]),0) BETWEEN ('2008/01/01') and ('2008/01/10')



The first does not return a value as '2008/01/10' is read as '2008/01/10 00:00:00' and not '2008/01/10 23:59:59'.
ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73027 Visits: 20314
Here's a comparison between the popular method recommended by Jack, and the two methods suggested by Orion Pax.


--===== Create and populate the test table
CREATE TABLE #DateTest (TestDate DATETIME NOT NULL)

INSERT INTO #DateTest
SELECT TOP 39652 CAST(n.number+0.5 AS DATETIME) AS TestDate
FROM dbo.Numbers n

--===== Add a Primary Key to maximize performance
ALTER TABLE #DateTest
ADD CONSTRAINT PK_TestDate PRIMARY KEY CLUSTERED (TestDate)


DECLARE @Startdate DATETIME, @Enddate DATETIME
SET @Startdate = '2001/01/01'
SET @Enddate = '2002/01/10'
--SELECT @Startdate, @Enddate -- Sanity check; yes, it's Jan 1 to Jan 10

SET STATISTICS TIME ON

PRINT '----- Orion Pax''s method 1 ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE CONVERT(VARCHAR(10), [TestDate], 111) BETWEEN @Startdate AND @Enddate -- VARCHAR(max)??

PRINT '----- Orion Pax''s method 2 ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE DATEADD(dd, DATEDIFF(dd, 0, [TestDate]),0) BETWEEN (@Startdate) and (@Enddate)

PRINT '----- Jack''s method ----------------'
SELECT COUNT(*)
FROM #DateTest
WHERE TestDate >= @Startdate and TestDate < @Enddate+1

SET STATISTICS TIME OFF



Results:

----- Orion Pax's method 1 ----------------
(1 row(s) affected)

SQL Server Execution Times:
CPU time = 413 ms, elapsed time = 413 ms.

----- Orion Pax's method 2 ----------------
(1 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 24 ms.

----- Jack's method ----------------
(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.



This is why Jack's method is recommended Wink

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Orion Pax
Orion Pax
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 350
Hi Chris,

My *recommendation* wasn't a comparison between my code vs. Jack's code. It was a recommendation on my own code.

The reason I added this is that Jack's method does not work for some of my existing systems as some data is loaded from text files with date values used for data retrieval but the source systems MySQL, Interbase, DB2, etc does not include the time value as the "DATE" datatype is used, thus date is "xxxx/xx/xx 00:00:00" in MS SQL. When Jacks method is used data for an additional day is retrieved. Which is incorrect. Jack's code whould have to be modified to
SELECT COUNT(*)
FROM #DateTest
WHERE TestDate >= @Startdate and TestDate < DATEADD(ss, -1, @Enddate+1)

which doesn't work but this does
SELECT COUNT(*)
FROM #DateTest
WHERE TestDate BETWEEN @Startdate and DATEADD(ss, 86399, @Enddate)


We have standardised on using
DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0)

because this cateres for
DATEADD(dd, DATEDIFF(dd, 0, [DateTime]), 0) = @InputDate


ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73027 Visits: 20314
The recommended method which Jack posted (where are ya, Jack?) will always run fast because the column in the WHERE clause on which it operates isn't subjected to any computation before it can be compared. This is significant whether or not the column is indexed, and more so if you're expecting the index to be used.

It baffles me to see code using a function on a column in a WHERE clause on a million-row table, when the computation could be applied - once - to the value which it's being compared to.

But you're correct Orion, Jack's method is specific for Datetime.

Cheers

ChrisM

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78826 Visits: 14970
Chris Morris (7/25/2008)
The recommended method which Jack posted (where are ya, Jack?) will always run fast because the column in the WHERE clause on which it operates isn't subjected to any computation before it can be compared. This is significant whether or not the column is indexed, and more so if you're expecting the index to be used.

It baffles me to see code using a function on a column in a WHERE clause on a million-row table, when the computation could be applied - once - to the value which it's being compared to.

But you're correct Orion, Jack's method is specific for Datetime.

Cheers

ChrisM


I'm still around. I just had not felt the need to reply until now.

Orion,

I posted a SQL Server specific solution because this is a SQL Server specific site and forum and SQL Server is what I work with. Now As Chris has said, using a computation on column in a WHERE clause is, in SQL Server, a bad idea. You will get much better performance in SQL Server by doing any needed modification to the parameter/variable than the column. This is also why, in columns that do not need time, I would set the time to 00:00:00.

As always a good discussion.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search