Skip to main content
A
Anonymous
June 15, 2007
Question

Searching on Dates

  • June 15, 2007
  • 1 reply
  • 269 views
I need to get a column back from a database called room_booked when the user types in a search based on a range of dates.

<cfquery datasource = "getroombooked" datasource = "rayannesql">
SELECT book_room
FROM booking
T
his is where I start to wonder do I do a BETWEEN range with a date something like

WHERE ((booking.bookcheckin_date BETWEEN #CreateODBCDate(form.arrivalDate)# AND #CreateODBCDate(form.departDate)#))

I then need to do a <cfif statement to see what rooms are booked.

<cfif book_room is not "">
<cfoutput>#book_room#>

Any help on this would be greatly appreciated
    This topic has been closed for replies.

    1 reply

    N
    Newsgroup_User
    Inspiring
    June 15, 2007
    yes, i think BETWEEN will do the trick.

    what you should pay attention to is the format of dates you pass to the
    createodbcdate function. unless you are requiring users to input dates
    in a certain format only and validate their input to make sure it is in
    the required format, then they could enter a date like 01/04/07 thinking
    they entered April 1st 2007 while your CF server may interpret in as
    January 4th 2007...

    another issue, more of a business logic nature, is you are checking in
    the query only if there is any check-in in the selected time period. i
    am not sure what sort of data you store in the bookcheckin_date column,
    but if it is only check-in date, then you query will return a room as
    available even if it is occupied on the date (but no check-in).

    just my thoughts, i am pretty sure you have it all thought out already
    and just posted only part of your code/logic.

    --

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices