need Help to write a query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 3-Mar-12 17:46pm

Sourav Sarkar-SS

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amitava Bag (Kolkata) · Accepted Answer · 2012-03-03T18:55:00

Solution 1

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+"'"

Posted 3-Mar-12 18:55pm

Amitava Bag (Kolkata)

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