We help IT Professionals succeed at work.

SQL Server Syntax - Search Date

baxtalo
baxtalo asked
on
I would like to search dates in my SQL Server database. My WHERE clause looks like this:
Where DayRequested like '%" & Request.Form("MyDay") & "%'
The date I'm searching for is in the database, but my query still won't return any results.
The date is 'datetime' in my database. Would you please let me know what's wrong with my query?
Thank you very much...
Comment
Watch Question

Author

Commented:
I've noticed that if I search 11/08/2011 it will find my date. But my date picker enters the date as 11/8/2011.
Is there a way to convert the date format somehow in the query so that it finds my date the way it is stored in the database?
2011-11-08 00:00:00.000
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
Use CONVERT(DATETIME, @yourdatetimevalue).

e.g.
You can put into the SQL Server script the date time from datepicker by replacing the string below with your date time value:

SELECT CONVERT(DATETIME, '11/8/2011')

Commented:
> Where DayRequested like '%" & Request.Form("MyDay") & "%'

Sounds like a very good opportunity for SQL-injection. Please adapt your code.

Anyway, for SQL-queries I prefer the format "yyyy/mm/dd", that almost never fails. The steps I would take are:
1. Convert the Request-value to a (sub)type Date. If that fails (for example when someone tries SQL-injection), report back to the user that he entered an invalid date.
2. Convert the Date to a string in the format "yyyy/mm/dd" by something like:
    Year(yourDate) & "/" & Month(yourDate) & "/" Day(yourDate)
    and use that string in the query.

Author

Commented:
I tried the following but shomething must be wrong with the syntax. It returns dates like 11/13/2011 but it wont' return days with single digits like 11/8/2011

Where CONVERT(varchar(10),DayRequested,101) like '%' + CONVERT(varchar(10),'%" & Request.Form("MyDay") & "%',101) + '%'
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
it is because you've converted it into a string rather than to a DATE at first. So in your code above you can change it like this:

Where CONVERT(varchar(10), CONVERT(DATE, DayRequested)) like '%' + CONVERT(varchar(10), CONVERT(DATE, '" & Request.Form("MyDay") & "'), 101) + '%'

You should be fine!!!

Author

Commented:
Unfortunately this won't return anything; not even the double digit days.
Senior Developer
CERTIFIED EXPERT
Commented:
Removed ,101 from the CONVERT and it should return you some value:

Where CONVERT(varchar(10), CONVERT(DATE, DayRequested)) like '%' + CONVERT(varchar(10), CONVERT(DATE, '" & Request.Form("MyDay") & "')) + '%'

Author

Commented:
Thank you very much, your solution is perfect. I've learned a lot.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.