BETWEEN Statement 2

[Viruland]

I want to write a SQL statement that brings up records between 2 dates. I've tried the following syntax but it doesn't work. Can somebody tell my what is wrong?

SELECT ID, Products, OrderDate FROM tblOrders
WHERE OrderDate BETWEEN #01/01/2002# AND #31/12/2002#
ORDER BY OrderDate;
Live fast, die young and leave a beautiful corpse behind.

 

[barcoder]

How about changing the SQL to check for dates

SELECT ID, Products, OrderDate FROM tblOrders
WHERE OrderDate BETWEEN cast('2002/01/01' as datetime) AND cast('2002/12/31' as datetime)
ORDER BY OrderDate;

 

[JamesLean]

Viruland,
While barcoder's query will work, your error arises from the fact you are using #s to delimit your date data. SQL Server uses single quotes for this. I would also provide the dates in an unambiguous format if you can, eg yyyymmdd:

SELECT ID, Products, OrderDate
FROM tblOrders
WHERE OrderDate BETWEEN '20020101' AND '20021231'
ORDER BY OrderDate

--James