T-SQL Query Help

I'm having trouble getting my queries to return appropriate results for the following queries though:

1. For a given specific date list the guests that are scheduled to check out, grouped by the villa type; additionally also present their count.

The guest reservation table has the following columns with data: (confirm_no, agent_id, g_name, g_phone)

The reservation table has the following columns with data: (confirm_no, credit_card_no, res_checkin_date, res_checkout_date, default_villa_type, price_plan).

SELECT g_name, default_villa_type
FROM guest_reservation, reservation
WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND res_checkout_date = 24/12/2010
order by default_villa_type, g_name;

SELECT COUNT(*) as total_checking_out
FROM reservation
WHERE res_checkout_date = 24/12/2010

Shouldn't that query work? When I run it, I get NO errors simply no results returned (and the count is 0), and l开发者_StackOverflow社区ooking at my table I know there should be 2 guest names returning as they checkout that day.

Help?

Remember to delimit your date literals with single quotes like '2010-12-24' and remember that datetime fields will take into account the time. Try something like this

WHERE res_checkout_date BETWEEN '2010-12-24' AND '2010-12-24 23:59:59.997'

or like this to get all records on the 24th of December, 2010, assuming the res_checkout_date is a datetime or smalldatetime field.

WHERE MONTH(res_checkout_date) = 12 AND DAY(res_checkout_date) = 24 AND YEAR(res_checkout_date) = 2010

Just a thought, but have you tried using DATEDIFF for the date comparison? This allows you to compare the date based on the day, and ignore the time:

WHERE DATEDIFF(d, '2010-12-24', res_checkout_date) = 0

T-SQL is reading your dates wrong. Date literals need to be formatted as string (i.e. '24/12/2010', notice the single quotes), whereas your dates are represented as integers (i.e. 24 divided by 12, which is divided by 2010).

So in your code, what you're actually doing is:

WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND res_checkout_date = 0

To that end, all you will need to do is wrap single quotes around your dates.

Also remember that specifying only a date assumes that you actually meant: 24/12/2010 00:00:00.000, and if the checkout date is actually 24/12/2010 00:01:00 (i.e. one minute past midnight), it won't be included in the result. To account for all times within a single day, do this:

WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND res_checkout_date >= '24/12/2010' AND res_checkout_date < '25/12/2010'

As a minor sidenote, I'd recommend that you use a more concise format for your dates. formatting your dates in dd/mm/yyyy format can be confusing and introduce subtle bugs into your code if you move your database onto a server with a different default language (e.g. Moving from a server where dates are formatted dd/mm/yyyy to a server where dates are formatted mm/dd/yyyy). To avoid this, I usually use the ANSI SQL date format, which is yyyy-MM-dd HH:mm:ss, and is not culture-dependent.

You need to enclose your date in quotes. As it is now, you're doing something analagous to where res_checkout_date = 0 (you're diving 24 by 12, which yields 2, then 2 by 2010, which truncates to 0)

Did you try putting date in '24/12/2010'

SELECT g_name, default_villa_type
FROM guest_reservation, reservation
WHERE
guest_reservation.confirm_no = reservation.confirm_no
AND convert(varchar,res_checkout_date,103) = '24/12/2010'
order by default_villa_type, g_name;

EDIT: I tested to put date without ' no result. When I added ' on date it showed me some results. How are you running your query?

try this :

    SELECT g_name, default_villa_type
    FROM guest_reservation, reservation
    WHERE
    guest_reservation.confirm_no = reservation.confirm_no
    AND res_checkout_date = '24/12/2010'
    order by default_villa_type, g_name;

    SELECT COUNT(*) as total_checking_out
    FROM reservation
    WHERE res_checkout_date = '24/12/2010'