DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply between two dates or datetimes for daterange?

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 

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

Related articles:

Does MS SQL Server's “between” include the range boundaries?

BETWEEN (Transact-SQL)

http://www.sqlusa.com/bestpractices/datetimeconversion/

Calculate the number of business days between two dates

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE