Execute the following Microsoft SQL Server T-SQL scripts in Query Editor to demonstrate how to use the BETWEEN operator for date and datetime range searches in SQL queries.
-- BETWEEN dates implementation for datetime OrderDate - QUICK SYNTAX
DECLARE @StartDate datetime ='20040201', @EndDate datetime = '20040205';
SELECT OrderCount=COUNT(*) FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate and OrderDate < DATEADD(DD,1,@EndDate);
-- 502
-- BETWEEN dates implementation for datetime OrderDate using DATE data type
DECLARE @StartDate date ='20040201', @EndDate date = '20040205';
SELECT OrderCount=COUNT(*) FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= @StartDate and OrderDate < DATEADD(DD,1,@EndDate);
-- 502
------------
-- BETWEEN datetime range QUICK SYNTAX
-- NOTE: equal operator would miss orders dated after midnight (on purpose or by mistake)
------------
-- Past midnight and next midnight
SELECT dateadd(dd, datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0),
dateadd(dd, 1+datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
-- 2016-10-23 00:00:00.000 2016-10-24 00:00:00.000
-- Orders for today (daterange 1 day)
SELECT * FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE OrderDate >= dateadd(dd, datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
AND OrderDate < dateadd(dd, 1+datediff(dd, 0, CURRENT_TIMESTAMP)+0, 0)
------------
------------
-- SQL Server count business days function / UDF - exclude Saturdays & Sundays
------------
CREATE FUNCTION ufnGetBusinessDays (@DateStart DATETIME,
@DateEnd DATETIME)
RETURNS INT
AS
BEGIN
IF ( @DateStart IS NULL
OR @DateEnd IS NULL )
RETURN ( 0 )
DECLARE @i INT = 0;
WHILE ( @DateStart <= @DateEnd )
BEGIN
SET @i = @i + CASE
WHEN datename(dw, @DateStart) IN (
'Saturday', 'Sunday' )
THEN 0
ELSE 1
END
SET @DateStart = @DateStart + 1
END -- while
RETURN ( @i )
END -- function
GO
SELECT dbo.ufnGetBusinessDays('2016-01-01', '2016-12-31')
-- 261
------------
------------
-- SQL datetime between examples using copy of Sales.SalesOrderHeader
------------
USE tempdb;
SELECT * INTO SOH FROM AdventureWorks2008.Sales.SalesOrderHeader
GO
-- Sales order count for the entire month of MARCH 2004
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-03-01 00:00:00.000'
AND OrderDate < '2004-04-01 00:00:00.000'
-- 2109
-- Equivalent datetime or date comparison queries
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-03-01'
AND OrderDate < '2004-04-01'
-- SQL date between - new in SQL Server 2008 - SQL date range between
SELECT COUNT(*) FROM SOH WHERE
CAST(OrderDate AS DATE) BETWEEN '2004-03-01' AND '2004-03-31'
-- SQL datetime between with explicit inclusive lower and upper limits
/***** WORKS BUT NOT BEST PRACTICES FOR ENTIRE DAYS *****/
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-31 23:59:59.997'
-- The following two queries work ONLY if all TIME parts are 00:00:00.000
/***** NOT BEST PRACTICES - WRONG RESULT IF TIME PART IS NOT 12:00AM *****/
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-31 00:00:00.000'
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-03-01' AND '2004-03-31'
-- datetime range / date range example for SQL between
-- TIME PART assumed to be 12:00AM = 00:00:00.000 not specified
-- SQL datetime between - SQL datetime comparison
SELECT COUNT(*) FROM SOH WHERE OrderDate BETWEEN '2004-01-02' AND '2004-01-07'
-- 344
-- Equivalent datetime range query
-- SQL between is inclusive operator - it includes the limits
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 00:00:00.000'
-- 344
-- Create an order 1 sec passed midnight on 2004-01-07
-- SQL datetime functions
UPDATE TOP(1) SOH SET OrderDate=DATEADD(ss, 1, OrderDate)
WHERE OrderDate='2004-01-07'
-- (1 row(s) affected)
-- The sales order with OrderDate = 2004-01-07 00:00:01.000 excluded
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 00:00:00.000'
-- 343
-- To include the entire day of 2004-01-07 specify upper limit time
SELECT COUNT(*) FROM SOH WHERE OrderDate
BETWEEN '2004-01-02 00:00:00.000' AND '2004-01-07 23:59:59.997'
-- 344
-- Alternate formulation with >=...AND...< double datetime comparison
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-01-02'
AND OrderDate < '2004-01-08'
-- 344
-- The new DATE type will work correctly with the between operator
-- SQL date between
SELECT COUNT(*) FROM SOH WHERE
CAST(OrderDate AS DATE) BETWEEN '2004-01-02' AND '2004-01-07'
-- 344
-- Equivalent datetime comparison query
SELECT COUNT(*) FROM SOH WHERE OrderDate >='2004-01-02 00:00:00.000'
AND OrderDate < '2004-01-08 00:00:00.000'
-- 344
-- Cleanup
DROP TABLE tempdb.dbo.SOH
GO
------------
------------
-- SQL datetime between T-SQL scripts using copy of HumanResources.EmployeePayHistory
------------
USE tempdb;
-- SQL select * into table
SELECT * INTO EPH
FROM AdventureWorks2008.HumanResources.EmployeePayHistory
GO
-- SQL select from new table - SQL datetime between - SQL date between
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '1997-11-01' AND '1998-01-05'
GO
/* Results
BusinessEntityID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:00.000
*/
-- SQL update select into table
UPDATE EPH SET RateChangeDate = '1998-01-05 00:00:01.000'
WHERE BusinessEntityID = 4
AND RateChangeDate = '1998-01-05'
GO
-- SQL between for DATE type
-- The following solution will NOT work correctly
-- The tacit assumption of TIME = 00.00.00.000 is no longer valid
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate BETWEEN '1997-11-01' AND '1998-01-05'
GO
/* Results
BusinessEntityID RateChangeDate
3 1997-12-12 00:00:00.000
*/
-- The following solution will work correctly
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE RateChangeDate >= '1997-11-01' AND
RateChangeDate < DATEADD(dd,1,'1998-01-05')
GO
/* Results
BusinessEntityID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:01.000
*/
-- Date type will work correctly - NOT SARGABLE
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE CONVERT(DATE,RateChangeDate) BETWEEN '1997-11-01' AND '1998-01-05'
GO
/* Results
BusinessEntityID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:01.000
*/
-- Date only string type will also work correctly - NOT SARGABLE
SELECT BusinessEntityID, RateChangeDate
FROM EPH
WHERE CONVERT(char(10), RateChangeDate,102) BETWEEN '1997.11.01' AND '1998.01.05'
GO
/* Results
BusinessEntityID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:01.000
*/
DROP TABLE tempdb.dbo.EPH
GO
----------
-- SQL date range between
----------
-- SQL between dates
USE AdventureWorks;
-- SQL between
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20040301' AND '20040315'
-- Result: 1112
-- BETWEEN operator is equivalent to >=...AND....<=
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-15 00:00:00.000'
/*
Sales orders with OrderDates
'2004-03-15 00:00:01.000' - 1 second after midnight (12:00AM)
'2004-03-15 00:01:00.000' - 1 minute after midnight
'2004-03-15 01:00:00.000' - 1 hour after midnight
are not included in the two queries above.
*/
-- To include the entire day of 2004-03-15 use the following two solutions
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20040301' AND OrderDate < '20040316'
-- SQL between with DATE type (SQL Server 2008)- NOT SARGABLE
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'
-- 1112
-- SQL between with DATE type (SQL Server 2008)- SARGABLE
-- Same result accidentally because time part of OrderDate is 0
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '20040301' AND '20040315'
-- 1112
-- SQL range with DATE type (SQL Server 2008)- SARGABLE
SELECT [Sales]=COUNT(*) FROM Sales.SalesOrderHeader
WHERE OrderDate >= CONVERT(DATE,'20040301') AND OrderDate < CONVERT(DATE,'20040316')
-- 1112
------------
No comments:
Post a Comment