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"))
cmdSelectAuthors = New sqlcommand("Select * From HotNews WHERE date_created=" & y, conpubs)
dtrAuthors = cmdSelectAuthors.ExecuteReader()
Response.Write(dtrAuthors("title") & "<br>")
Another alternative is to change the default for "date_created" from
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).
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...