Click here to Skip to main content
15,313,894 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


RoomBooking
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.
SQL
"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+"'"
Posted
Comments
kishore sharma 4-Mar-12 0:57am
   
Hello,Mr.sourav
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

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

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