August 11, 2016 at 7:33 am
Hi guys
please look at my query below and comments:
CREATE TABLE TempDate(
RowID INT NULL,
Workorder VARCHAR(10) NULL
,RequiredByDate DATETIME
)
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '20160801'
SET @EndDate = '20160805'
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (1,'S12921','2016-08-01 00:00:00.000')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (2,'S14287','2016-08-01 04:20:57.260')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (3,'S13372','2016-08-01 04:20:57.260')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (4,'S13321','2016-08-02 00:00:00.000')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (5,'S13322','2016-08-02 11:19:38.440')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (6,'S13333','2016-08-03 04:44:29.617')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (7,'S13334','2016-08-04 03:20:40.000')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (8,'S13326','2016-08-05 00:00:00.000')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (9,'S13246','2016-08-05 04:38:51.003')
INSERT INTO TempDate (RowID,WorkOrder,RequiredByDate) VALUES (10,'S13358','2016-08-06 00:00:00.000')
SELECT WorkOrder,RequiredByDate FROM TempDate WHERE RequiredByDate BETWEEN @StartDate AND @EndDate
according TO the query the results IS RowID 1 TO 8 but i need TO INCLUDE RowID 9 because its also ON the 9th
IF i USE the query
SELECT WorkOrder,RequiredByDate FROM TempDate WHERE RequiredByDate BETWEEN @StartDate AND @EndDate+1
the query RETURNS everything whereas RowID 10 should be excluded
how do i FORMAT the above queries IN ORDER TO RETURN the correct DATA based ON the parameters.
August 11, 2016 at 8:30 am
Consider this query:
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '20160801'
SET @EndDate = '20160805'
SELECT startdate = @StartDate, enddate = @EndDate;
Results:
startdate enddate
----------------------- -----------------------
2016-08-01 00:00:00.000 2016-08-05 00:00:00.000
You're running this:
SELECT WorkOrder,RequiredByDate
FROM TempDate
WHERE RequiredByDate BETWEEN '2016-08-01 00:00:00.000' AND '2016-08-05 00:00:00.000'
2016-08-05 04:38:51.003 is not between '2016-08-01 00:00:00.000' AND '2016-08-05 00:00:00.000' but 2016-08-05 IS between 20160801 and 20160805. Try this instead:
DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
SET @StartDate = '20160801'
SET @EndDate = '20160805'
SELECT WorkOrder,RequiredByDate
FROM TempDate
WHERE CAST(RequiredByDate AS date) BETWEEN @StartDate AND @EndDate;
-- Itzik Ben-Gan 2001
August 11, 2016 at 8:43 am
The problem here is that dates without times represent midnight on that date, so when you are looking for records between your two parameters, you are excluding records that fall after midnight on your end date. There are three options.
1) CAST your RequiredByDate to a DATE. This may not be SARGable in SQL2008. I know that CASTing a datetime to a date is SARGable in some versions of SQL Server, but I'm not sure which versions.
2) Specify the time on your end date. The problem here is that you need to specify the correct precision or you may still miss records. For instance, if you specify '2016-08-11 23:59:59' you will miss records with times like '2016-08-11 23:59:59.50'.
3) Use half-closed intervals. The BETWEEN operator uses closed-intervals (intervals that include both end points), but when working with time, you usually want to use half-closed intervals (intervals that include only one end point [usually the beginning]).
I tend to use the third option, but the first option is also a viable option. I won't even consider the second option.
Here's how you would rewrite the query the WHERE clause
-- Original query
SELECT WorkOrder,RequiredByDate
FROM TempDate
WHERE RequiredByDate BETWEEN @StartDate AND @EndDate
-- Casting as DATE
SELECT WorkOrder,RequiredByDate
FROM TempDate
WHERE CAST(RequiredByDate AS DATE) BETWEEN @StartDate AND @EndDate
-- Specifying the time on your end date
-- Don't even bother using this approach
-- Half-closed interval
SELECT WorkOrder,RequiredByDate
FROM TempDate
WHERE RequiredByDate >= @StartDate
AND RequiredByDate < @EndDate + 1
Drew
Edit: Forgot the + 1 on the last query.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2016 at 9:39 am
To alleviate the SARGability problem with the "WHERE CAST(RequiredByDate AS DATE) BETWEEN @StartDate AND @EndDate" solution Drew and I provided you could create an indexed view like so:
CREATE VIEW dbo.vw_TempDate
WITH SCHEMABINDING AS
SELECT WorkOrder,
RequiredByDate = CAST(RequiredByDate AS date)
FROM dbo.TempDate;
CREATE UNIQUE CLUSTERED INDEX vci_vw_TempDate
ON dbo.vw_TempDate(RequiredByDate, WorkOrder);
Then you would get a Clustered Index Seek using this query:
SELECT RequiredByDate, WorkOrder
FROM dbo.vw_TempDate WITH (NOEXPAND)
WHERE RequiredByDate BETWEEN @StartDate AND @EndDate;
-- Itzik Ben-Gan 2001
August 12, 2016 at 1:11 am
Thank you so much guys for your solutions, it worked perfectly!!
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply