Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: specifying a date in a where clause

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it won't work because in general dates aren't strings, they're dates

    what database are you using? mysql? sybase? db2? postgresql? interbase?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    I'm using SQL Server 2000

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what datatype is the SentenceDispositionDate column?

    and do its values contain time components? do you use GETDATE() to populate the values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    it is a datetime datatype

    no we do not use a GETDATE()

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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")')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    neither of those are working. i keep getting 'expected end of statement' errors.

    i'm also using classic asp, not asp.net
    Last edited by bla4free; 03-07-05 at 14:06.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Posts
    165
    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.

  10. #10
    Join Date
    May 2004
    Posts
    125
    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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2005
    Posts
    165
    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.
    Last edited by bla4free; 03-07-05 at 16:48.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2005
    Posts
    165
    if i do that i get an unexpected end of statemente error.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please show the entire query when you "do that"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •