July 21, 2008 at 10:02 am
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?
July 21, 2008 at 10:59 am
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
Consultant - Straight Path Solutions
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
July 21, 2008 at 10:59 am
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
---------------------------------------------------------------------
July 21, 2008 at 1:19 pm
Thanks to both of you, I knew it was something simple.
July 24, 2008 at 3:12 am
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'.
July 25, 2008 at 3:15 am
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 😉
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
July 25, 2008 at 4:41 am
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
July 25, 2008 at 4:57 am
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
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
July 25, 2008 at 6:45 am
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
Consultant - Straight Path Solutions
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply