Click here to Skip to main content
15,300,707 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am developing a website on hotel.
There is a concept of Room Booking
User can book there room from this page.

RoomDetails table

RoomCategory Roomno

User Roomno ArrivalDate DepartDate

Now I want to Write a Query to get the those rooms only those are not booked on the date Choose by the user.

I write this Query. but not getting expected output.
"select top " + DropDownList5.SelectedValue + " roomno from roomdetails where roomno not in (select roomno from roombooking where ((ArrivalDate between '"
                      + dt1 + "' and '" + dt2 + "') or (DepartureDate between '"
                      + dt1 + "' and '" + dt2 + "'))and  Category ='"+DropDownList2.SelectedValue+"') and RoomCategory='"+DropDownList2.SelectedValue+"'"
kishore sharma 4-Mar-12 0:57am
In such kind of date Query's most of us get struck wiht the date format,
We dont check that Sqlserver or mysql supports wihc date format.We may pass dateformat as dd/mm/yyyy but server date format may be mm/dd/yyyy
so if you pass 03/02/2012(dd/mm/yyyy) and server consider it as mm/dd/yyyy means it will check for march month instead of feb which we expect.
So please ensure that you are converting date format of the server before executing query.

--Love Errors
Amitava Bag (Kolkata) 4-Mar-12 1:27am
For sql server it is my approach to format the date is in dd/MMM/yyyy format and concatenate with the sql string (I assume that <dt> is in such format). But if your software will run in multilingual mode then it should be used in MM-dd-yyyy format.

1 solution

    top " + DropDownList5.SelectedValue + " roomno
 from roomdetails
    roomno not in
            select roomno
            from roombooking
                        '" + dt1 + "' between ArrivalDate and DepartureDate
                and  Category ='"+DropDownList2.SelectedValue+"'
        and RoomCategory='"+DropDownList2.SelectedValue+"'"
[no name] 4-Mar-12 12:33pm
gr8 answer
This is new for me value as condition and column name as value
[no name] 4-Mar-12 12:33pm
thank you so much
Amitava Bag (Kolkata) 5-Mar-12 12:39pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900