|
Execute the following
Microsoft SQL Server T-SQL script in Management Studio Query Editor to demonstrate correct use of date-only string. In sorting or comparing only the YYYYMMDD date-only string evaluates correctly.
USE AdventureWorks
GO
-- Evaluates correctly, SQL Server converts date literals to datetime
-- The assumption is that the time part of datetime is 0 - otherwise BETWEEN does not work
SELECT soh.SalesOrderID,
OrderDate = convert(CHAR,OrderDate,101),
ProductName = p.Name,
ListPrice = '$' + convert(VARCHAR,ListPrice,1)
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE OrderDate BETWEEN '07/01/2003' AND '07/31/2003'
GO
-- (3600 row(s) affected)
-- Evaluates INCORRECTLY as string date-only string compare
-- Not sargable where clause - for demo only
SELECT soh.SalesOrderID,
OrderDate = convert(CHAR,OrderDate,101),
ProductName = p.Name,
ListPrice = '$' + convert(VARCHAR,ListPrice,1)
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE convert(VARCHAR,OrderDate,103) BETWEEN '07/01/2003' AND '07/31/2003'
GO
-- (2152 row(s) affected)
-- Evaluates correctly as date-only string compare
-- Not sargable where clause - for demo only
SELECT soh.SalesOrderID,
OrderDate = convert(CHAR,OrderDate,101),
ProductName = p.Name,
ListPrice = '$' + convert(VARCHAR,ListPrice,1)
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE convert(VARCHAR,OrderDate,112) BETWEEN '20030701' AND '20030731'
GO
-- (3600 row(s) affected)
Related article:
http://www.sqlusa.com/bestpractices2008/between-dates/
|
