Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BETWEEN date1 AND date2 Not returning data for date2 Expand / Collapse
Author
Message
Posted Monday, July 21, 2008 10:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 7, 2009 5:30 PM
Points: 14, 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?
Post #537785
Posted Monday, July 21, 2008 10:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #537841
Posted Monday, July 21, 2008 10:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 5,975, Visits: 12,878
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


---------------------------------------------------------------------

Post #537842
Posted Monday, July 21, 2008 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 7, 2009 5:30 PM
Points: 14, Visits: 24
Thanks to both of you, I knew it was something simple.
Post #537939
Posted Thursday, July 24, 2008 3:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 3:14 AM
Points: 52, Visits: 316
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'.
Post #539942
Posted Friday, July 25, 2008 3:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
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 ;)


“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
Post #540759
Posted Friday, July 25, 2008 4:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 3:14 AM
Points: 52, Visits: 316
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

Post #540796
Posted Friday, July 25, 2008 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Post #540804
Posted Friday, July 25, 2008 6:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #540860
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse