search the database to get date between using mssql

Question

0.00/5 (No votes)

See more:

C#

SELECT *
FROM Room
LEFT JOIN booking ON (Room.RoomId = booking.RoomId)
WHERE booking.Roomid is null
  AND GETDATE() BETWEEN bookin.checkindate '" + TxtCheckIn.Text + "'
                AND booking.checkoutdate  '" + TxtCheckOut.Text + "'" + "
ORDER BY Room.RoomType

/*I want to check in the booking table if the date matches the checkin and checkout dates selected by users. If it doesn't match, the query should show all rooms in the room table (even if it is in the booking table), provided that they have different dates.*/

Posted 25-Jul-13 1:01am

Difameg Network Nigeria

Add a Solution

Comments

OPees 25-Jul-13 7:19am

What is datatype of bookin.checkindate and booking.checkoutdate in table?
if its not datetime use convert method like convert(datatime,bookin.checkindate,103)
then compare

// Here 103 datetime format like 25/07/2013

2 solutions

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)

Chui PuiKwan · Answer 1 · 2013-07-25T01:58:00

Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49

check in the booking table if the date matches the checkin and checkout dates selected by users.

first:

SQL

SELECT *
FROM Room
LEFT JOIN booking ON (Room.RoomId = booking.RoomId)
WHERE booking.Roomid is null
  AND  CONVERT(varchar(100), GETDATE(), 120) BETWEEN bookin.checkindate '" + TxtCheckIn.Text + "'
                AND booking.checkoutdate  '" + TxtCheckOut.Text + "'" + "
ORDER BY Room.RoomType

second:

If it doesn't match, the query should show all rooms in the room table (even if it is in the booking table), provided that they have different dates.

select * from Room where ......

Difameg Network Nigeria · Answer 2 · 2013-07-25T04:22:00

I have tried it but i am having error

Incorrect syntax near '07/25/2013'.

SQL

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '07/25/2013'.

above is the error i get, thank, how do i resolve
thanks for your time, i really do appreciate

search the database to get date between using mssql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0