specifying a date in a where clause

bla4free · #1 (**permalink**) 03-07-05, 12:14

hi. i'm trying to setup a page which will allow user to search events by date. i have a regular html page which will pass the information to my asp page. my where clause looks like this but it will not work:

Code:

WHERE DefendantCharge.SentenceDispositionDate LIKE '%" & Request.Form("Date") & "%'

any ideas why this won't work? thanks in advance!

r937 · #2 (**permalink**) 03-07-05, 12:31

it won't work because in general dates aren't strings, they're dates

what database are you using? mysql? sybase? db2? postgresql? interbase?

bla4free · #3 (**permalink**) 03-07-05, 12:35

I'm using SQL Server 2000

r937 · #4 (**permalink**) 03-07-05, 12:37

what datatype is the SentenceDispositionDate column?

and do its values contain time components? do you use GETDATE() to populate the values?

bla4free · #5 (**permalink**) 03-07-05, 12:40

it is a datetime datatype

no we do not use a GETDATE()

r937 · #6 (**permalink**) 03-07-05, 12:45

then you could probably just say this --

Code:

 ... WHERE DefendantCharge.SentenceDispositionDate 
             = 'Request.Form("Date")'

assuming all your datetimes have 00:00:00 time components

otherwise, you'd want to do it this way --

Code:

 ... WHERE DefendantCharge.SentenceDispositionDate 
             >= 'Request.Form("Date")'
       and DefendantCharge.SentenceDispositionDate 
             <  dateadd(d,1,'Request.Form("Date")')

bla4free · #7 (**permalink**) 03-07-05, 13:00

neither of those are working. i keep getting 'expected end of statement' errors.

i'm also using classic asp, not asp.net

r937 · #8 (**permalink**) 03-07-05, 13:19

well, SQL Server does not require a statement terminator, the SQL statement ends after the last word in the statement, so i guess i will have to give way to someone more knowledgeable, because i don't know either classic asp or dot net, and it sounds like that's where your problem is now

the only reason i thought i could help is because it sounded like an SQL problem

you can verify whether the SQL works by running it in Query Analyzer

bla4free · #9 (**permalink**) 03-07-05, 13:23

i know my statement works with a date in it...i'm just having a hard time searching by date through ASP. i do appreciate your help though.

DMWCincy · #10 (**permalink**) 03-07-05, 14:41

If you are not worried about the time, just the date...try this:

WHERE Convert(char(8), DefendantCharge.SentenceDispositionDate , 112)
= convert(char(8), " & Request.Form("Date") & ",112)"

what this will do is convert the date over to YYYYMMDD format. As long as you are not worried about time this should work. Also, if you have to do a range of dates, this particular format will sort properly.

If you still get an error, do a response.write(sqlstring) of the select string before sending it to be executed. Post that string and we can better help you out.

HTH
DMW

r937 · #11 (**permalink**) 03-07-05, 15:14

the problem with using a function on the date column is that it renders that part of the WHERE clause non-sargable

this means you get a table scan, and can't use the index (if any) on the date column

bla4free · #12 (**permalink**) 03-07-05, 15:37

This is my sql string as of right now.

Code:

strSQL = "SELECT DefendantCharge.SentenceDispositionDate, DefendantCase.LabNo, DefendantCase.FirstName, DefendantCase.MiddleName, DefendantCase.LastName, PubDefDescription.[Description] AS PubDef, DefendantCharge.ChargeCount, DispositionDescription.[Description] AS Disposition, DefendantCharge.SentenceNotes FROM DefendantCase JOIN DefendantCharge ON DefendantCharge.VBKey=DefendantCase.VBKey LEFT JOIN DispositionDescription ON DefendantCharge.SentenceDisposition=DispositionDescription.DispositionID LEFT JOIN PubDefDescription ON PubDefDescription.PubDefID=DefendantCase.PubDefID WHERE Convert(char(8), DefendantCharge.SentenceDispositionDate, 112) = convert(char(8), " & Request.Form("Date") & ",112) ORDER BY DefendantCase.LastName, DefendantCase.FirstName"

it runs the query without any errors; however, it will not pull up any results. i get a blank screen when i know i should have about 30 results. thanks for any help.

r937 · #13 (**permalink**) 03-07-05, 15:49

you are missing the single quotes around the date string

but you don't have to convert the date string to CHAR(8), it's already a string (assuming you put single quotes around it)

also, allow me to suggest again that if you put a column into a function, you will get a table scan

bla4free · #14 (**permalink**) 03-07-05, 15:56

if i do that i get an unexpected end of statemente error.

r937 · #15 (**permalink**) 03-07-05, 16:13

could you please show the entire query when you "do that"

Internet Services

Web Development

Digital Marketing

Consumer Tech