How do you compare date values with MS SQL Server

[comn8u]

I'm stuck trying to compare date values from users selection against a database. I also need to add a condition statement on if the results return no match. How would I do that?

Also, I'm not even sure if my SELECT statement is right. The user will hit a date that will be in format m/d/yyyy and that will be compared to see if it exist in the database. The datebase column "date_created" is default with date/time( 8 ). But when I compare the to together, nothing returns back. The page loads successfully, but no results from the database.

Here is my code:
dim conpubs as sqlconnection
dim cmdSelectAuthors as sqlcommand
dim dtrAuthors As sqlreader

conpubs = New sqlconnection(configurationsettings.appSettings("STD"))
conpubs.open()
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write("<table><tr><td>")

While dtrAuthors.Read()
Response.Write(dtrAuthors("title") & "<br>")
End While

Response.Write("</td></tr></table>")

dtrAuthors.close()
conPubs.close()

[PeOfEo]

http://msdn.microsoft.com/library/de...iFunctions.asp
That may be useful. But you are going to just be comparing the dates, just use the now function for current and then say if they are = then do something. That link is for vbscript, not vb.net... Ill hunt around for the vb.net one, but those should be the same.
http://msdn.microsoft.com/library/de...nctionsVBA.asp here is visual basic

[CardboardHammer]

The problem is that you're comparing a date in a string against a datetime datatype, which contains date AND time.

Try this:

... WHERE DATEDIFF(day, date_created, '" & y & "') = 0)"

[CardboardHammer]

Another alternative is to change the default for "date_created" from

GETDATE()

to

CONVERT(varchar(10), Get_Date(), 101)

The convert (followed by an implicit cast) will give you the date with no time, which will match correctly to the strings you are using right now. You would have to update all the current data in the database for it to work on both the old as well as the new.

... SET date_created = CONVERT(varchar(10), date_created, 101)


If you do go with this, you should consider using smalldatetime instead of datetime, as it's half the size and will go further into the future than datetime, though it doesn't go back before 1900 (which probably isn't a concern to your application, but you'd know better than I).

[praveenbattula]

for compare date only in date time field in sql do not use either cast or convert. They are not efficient and not recommended. So, here is the complete solution which works perfect.
Compare date only in date time field
Let me know, if any issues still...