Date/Time Problem

[koeniga]

I have a SQL Server 2000 database that extracts rates due to times of year.
My problem is that when users extract times not set in the parameter there is
no return of rate.
My SQL is

FROM dbo.houserate
WHERE (house = '#pid#') AND (Startdate <=
'#Dateformat(input1,"mm/dd/yyyy")# #timeformat(rightnow, "16:00:00")#') AND
(Enddate >= '#Dateformat(input2,"mm/dd/yyyy")#')

What is my best solution to fix this?

[Dan Bracuk]

So you have a form with two dates and you want to user proof it? This happens
to us so often that I wrote a custom tag for it. It was written when we were
using version 4.5 so it could be improved upon, but the logic is:

1. make sure both form fields are actual dates
2. make sure they are in the right order
3. make sure they fall within a pre-defined range.
4. make sure they are not too far apart.

Write some code to address all four of these, and you'll be fine. The version
7 version of cfform makes the first one unnecessary.

[TurboMini]

also consider #createodbcdate()# and #createodbcdatetime()# and #isdate()# if you are doing server-side validation of the dates before running the SQL query