Execute the following script in 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
SELECT soh.SalesOrderID,
OrderDate=convert(char,OrderDate,101),
ProductName = p.Name,
ListPrice = '$'+convert(varchar,ListPrice,1)
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
JOIN Production.Product p
ON sod.ProductID= p.ProductID
WHERE OrderDate BETWEEN '07/01/2003' AND '07/31/2003'
GO

-- Evaluates INCORRECTLY as string date-only string compare
SELECT soh.SalesOrderID,
OrderDate=convert(char,OrderDate,101),
ProductName = p.Name,
ListPrice = '$'+convert(varchar,ListPrice,1)
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
JOIN Production.Product p
ON sod.ProductID= p.ProductID
WHERE convert(varchar,OrderDate,103) BETWEEN '07/01/2003' AND '07/31/2003'
GO

-- Evaluates correctly as date-only string compare
SELECT soh.SalesOrderID,
OrderDate=convert(char,OrderDate,101),
ProductName = p.Name,
ListPrice = '$'+convert(varchar,ListPrice,1)
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON sod.SalesOrderID = soh.SalesOrderID
JOIN Production.Product p
ON sod.ProductID= p.ProductID
WHERE convert(varchar,OrderDate,111) BETWEEN '2003/07/01' AND '2003/07/31'
GO