datetime query

my database contains 1 field with "datetime" in sql server
hw can i query the database to just compare the date section of that field ? and not the time
WHERE CONVERT(VARCHAR(10),datecolumn,101) >= '08/06/2005'
|||Although Dinakar's suggestion will work, it will not perform as well as something like this:

WHERE datecolumn >= '20050806' AND datecolumn < '20050807'

When you perform a function on a column (such as CONVERT) this willmake the query non-sargable and will therefore not take advantage of anindex on the column.
For more tips on performance, seeSQL Server Transact-SQL WHERE Clause.

Datetime issue

I am trying to run a query to get items that expire within the next 3 days. I got this off of another section of this website, but it will not work in Microsoft SQL Server 2000-Any ideas? Thanks.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(CURRENT_DATE, INTERVAL 3 DAY) AND CURRENT_DATEYou need to look up the syntax of DATEADD in Books Online.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(day, 3, CURRENT_DATE) AND CURRENT_DATE|||Okay-didn't think to do that-I had just done a datetime search-Would I be correct that the following would return anything within 7 days?

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATEADD (DD, 7, GETDATE()) AND GETDATE()

It isn't returning any data, so if I am doing correctly, I have something wrong in my insert statement.

Thanks.|||wherever you got that from, it doesn't work

not even if you convert the syntax to sql server syntax :)

the reason is, the dates need to be reversed

this will not work --

... where perishable_date between dateadd(day,3,current_date) and current_date

this might --

... where perishable_date between current_date and dateadd(day,3,current_date)

the reason i say "might" is because you need to find what microsoft sql server uses instead of the standard sql current_date function

;)|||whoops, you already found GETDATE() -- congratulations!

now just change your query around and you'll be in business|||I switched the dates and it works. Thank you.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN GETDATE() AND DATEADD(DD, 7, GETDATE())