0
<cfquery name="Booking_Link" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
    SELECT *
    FROM Bookings
    WHERE BookingDateIN AND BookingDateOUT NOT BETWEEN <cfqueryparam value="#FORM.SelDateIN#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#FORM.SelDateOUT#" cfsqltype="cf_sql_date">
</cfquery>

This works .... But if my booking date(UK - dd/mm/yyyy) has a range of 05/02/2010 to 09/02/2010

And my search value is 04/02/2010 to 05/02/2010 ... this don't work :(.

I can understand why so I thought of

<cfquery name="Booking_Link" datasource="HotelBookingSystem" username="HBSuser" password="HBSpass">
    SELECT *
    FROM Bookings
    WHERE BookingDateIN AND BookingDateOUT NOT BETWEEN <cfqueryparam value="#FORM.SelDateIN#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#FORM.SelDateOUT#" cfsqltype="cf_sql_date">
    AND BookingDateIN != <cfqueryparam value="#FORM.SelDateIN#" cfsqltype="cf_sql_date">
    AND BookingDateOUT != <cfqueryparam value="#FORM.SelDateOUT#" cfsqltype="cf_sql_date">
</cfquery>

But this doesn't work :(, So is there another way of doing this ?

I have tried the >= and <= way as well , but that also has problems.

Edited by Cheesecake87: n/a

4
Contributors
3
Replies
7
Views
7 Years
Discussion Span
Last Post by arrgh
0

I had that same problem with searching between dates. here is what i used.

SELECT * FROM db.table WHERE ((date BETWEEN #CreateODBCDate(URL.from)# AND #CreateODBCDate(URL.to)#))

i used url because i was using a url to pass the variables and the "date" is in my database, so adjust to what you have for your date fields. i hope this helps at least.

0

(UK - dd/mm/yyyy

This is an old thread.

But for anyone reading, the most important problem is the date format. CF assumes US date format by default. So while you think you're passing May 4th (4/5/2010), the date you're actually passing is April 5th! Use LS date functions to interpret the date string properly.

Once that is corrected .. then you can figure out what else is wrong with the sql ...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.