SQL Query and ColdFusion

[Cheesecake87]

<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.

[utdream]

This is more of a SQL issue then a CFML issue.

Try using <> for your NOT EQUAL statements. The != is more PHP'ish. ;)

http://www.w3schools.com/sql/sql_where.asp

Hope this helps.

[fobos]

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.

[arrgh]

(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 ...