sql query to compare the dates in the existing table to the selected date?

i need to compare the dates in a existing table with the selected date for equality. I am developing an asp.net application and i am tying to insert a new record in the already existing table and i am using a开发者_JAVA技巧jax calender extender in that asp.net form where i need to compare the selected date with already existing dates in order to display the records containing the same dates in a gridview even before i add the new record.

I don't completely understand where the problem is....

DECLARE @YourDate DATETIME
SET @YourDate = '2010-11-09'

SELECT (list of columns)
FROM dbo.YourTable
WHERE SomeDateColumn = @YourDate

The main issue you need to be aware of: in SQL Server up to version up to version 2005, you only have the DATETIME data type, which as its name implies stores both date and time - so an exact match will match down to the millisecond.... often this is not what you really want.

Can you explain in more detail what you're trying to achieve and what the trouble is you're having??

If you're only interested in equality on the DATE element, ignoring the time present within a column value, then use a clause like this:

DECLARE @DateParam DATETIME
SET @DateParam = '20101109'

SELECT SomeField
FROM SomeTable
WHERE DateField >= @DateParam AND DateField < DATEADD(dd, 1, @DateParam)

This example would find all records where DateField column has a date(& time) at any point in the mentioned day.

I would totally go with the solutions from marc_s and AdaTheDev (and their and my understanding of what you need - you need to define in your question what your selected date is)

But assuming your selected input is a date (as in the other answers) with format of yyyy-mm-dd (marc_s) or yyyymmdd (adathedev), the following alternative should also work

DECLARE @Date DATETIME
SET @Date = '2010/10/10'

SELECT * 
FROM SomeTable
WHERE CONVERT(VARCHAR(10), DateField, 126) = @Date

When you Declare datetime and set it using only the date - the time is set to 12:00:00.000. So with an equality operator it will look for that exact match with the time (which is highly unlikely if you're inserting into the table with something like GETDATE()).

My solution simply takes the time off the fields in the database when querying. But I would still go with the other solutions. This is simply an alternative.

+1 to the other guys.

http://www.sql-server-tool.com/compare-two-tables.htm CHECK THE SITE