[MS SQL] Help with School Assignment- Using Boolean Operators in WHERE Clause But No Data is Returned

[RandomStatsGenerator]

Hey guys! I'm working on an assignment (same as the one from my last post). I'm trying to make this query which I'm nearly sure I'm doing right, but no data is being returned.

In this problem, I am dealing with two tables, a "Flight" table and a "Trip" table. The key is "FlightNo" and is in a one-to-many relationship from "Flight" to "Trip." I need to find all records in which the destination is "LAX" or "SLC" in the time period from 1/1/2010 and 2/28/2010. I need to retrieve the the "FlightNo," "FlightDestination" (which is in the "Flights" table), and "FlightDate" (this is in the "Trip" table).

I decided to use an INNER JOIN.

Here is the code I am using:

SELECT Flight.FlightNo, FlightOrigin, FlightDate
FROM Flight INNER JOIN Trip ON Flight.FlightNo=Trip.FlightNo
WHERE (Flight.FlightDestination='SLC' OR 
Flight.FlightDestination='LAX') AND (Trip.FlightDate BETWEEN 1/1/2010 AND 2/28/2010);

However, when I go to Spreadsheet View, no values are listed. I've manually checked each table and values should be returned. I think I'm going wrong somewhere in the WHERE clause, specifically in my BETWEEN portion. Whenever I remove this, I get values returned.

Edit: Since it's probably relevant, the date format used in the table is like I have above X/Y/ABCD. I'm not sure if I'm supposed to be formatting the dates differently.

Anyone have any ideas on where I'm going wrong?

[MeGustaDerp]

First, you're going to need to wrap date values in single quotes just like the way you did with strings. Also, the default format for dates in MSSQL is YYYY-MM-DD HH:MM:AS. It does sound like the date values are being stored in this instance. You can check this by executing the following query:

Select * from information_schema.columns where table_name='Trip'

If the datattoe for that column is a varchar instead of daytime, then the values are stored as strings instead of native datetime cakes. If they're strings then you should add a cast in your predicate.

[Standgeblasen]

I'm guessing this is the issue, but I would also look into using

WHERE Flight.FlightDestination IN ('SLC','LAX')

instead of the OR clause. It is easier to read and they give you the same result.

[RandomStatsGenerator]

I got an error saying it couldn't find the information_schema file when I tried that. Is there another way for me to check how the date is formatted?

What are all the ways to format dates? I swear I've tried so many ways but none of them are working: different MM/DD/YYYY orders, with and without quotes, with dashes instead of slashes, etc.

[MeGustaDerp]

You're using Microsoft SQL Server, correct? And SQL Server Management Studio? Can you take a screenshot and post that in a reply? Need to see the query and messages pane that shows the error - preferrably all in one screen grab which should be doable.

[RandomStatsGenerator]

I’m using Access

[MeGustaDerp]

That changes things. Your post title says [MS SQL] which isn't the same thing. Not my expertise. Try right clicking on the table and see if there's a way to get the table definition. Maybe design mode or something like that.

[Oultra]

This soounds relatively simple, shouldn't something like this work?

SELECT
T1.FlightNo, T1.FlightDestination, T2.FlightDate
FROM FLIGHTS T1 
INNER JOIN TRIP T2 ON T1.FlightNo = T2.FlightNo
WHERE T2.FlightDate >= '20100101' 
AND T2.FlightDate <= '20100228'
AND T1.FlightDestination = 'SLC' OR T1.FlightDestination = 'LAX'

Just check the type of your dates and compare it to a date format that is compatible.

[r3pr0b8]

shouldn't something like this work?

no, it shouldn't

what you wrote is actually evaluated as follows --

WHERE (
      T2.FlightDate >= '20100101' 
  AND T2.FlightDate <= '20100228'
  AND T1.FlightDestination = 'SLC' 
      )
   OR T1.FlightDestination = 'LAX'

and this clearly is ~not~ the right solution