SQL Server 'views' and between

Hey sql newb here, I have this problem which I am suppose to create a view which contains the information of different bookings of a product between a certain time frame (march - April)

I came up with this, but instead of displaying the specific time frame it's just showing everything

CREATE VIEW sailors_view1 
AS
   SELECT sailors.sid, sname, rating
   FROM sailors, reserves
   WHERE start_date BETWEEN '8 March, 2007' AND '14 April开发者_运维技巧, 2007'
   With Check Option
GO

and I'm using this to test: SELECT * From sailors_view1

Update: table definitions

CREATE TABLE sailors 
  (sid CHAR(5), 
   fname VARCHAR(10), sname VARCHAR(10), 
   street_address VARCHAR(20), 
   suburb VARCHAR(20), 
   rating INT, age INT, phone CHAR(3)) 

CREATE TABLE dbo.reserves 
  (sid CHAR(5), 
   bid CHAR(3), 
   start_date DATETIME, 
   end_date DATETIME, 
   rname VARCHAR(10)) 

---

You have an implicit cross join in your from clause which will certainly give you more records than you expect. You should probably either use a standard join clause (inner join, left, right, etc) or include a join condition in your where clause as in:

where sailors.sid = reserves.sid

Also, I don't recall seeing a date format like that in sql server. Is your start_date column a datetime data type, or is it a char/varchar?

Even with both of those issues, I'm not sure they would explain why you're getting all records, but I would check on them and see if you get closer to the answer.

---

I would recommend not using the old-style JOIN with just table after table, separated by commas. This leads to unexpected results if you forget (like you did) to define a join condition in the WHERE clause.

Please always use the new ANSI-style JOIN syntax - something like this:

CREATE VIEW sailors_view1 
AS
   SELECT 
       s.sid, s.sname, r.rating
   FROM 
       dbo.sailors s 
   INNER JOIN
       dbo.reserves r ON s.sid = r.sid
   WHERE 
       r.start_date BETWEEN '20070308' AND '20070414'
   WITH CHECK OPTION
GO

With this explicit INNER JOIN and a ON s.sid = r.sid clause, you clearly state your intent of joining those two tables, and using the sid column as the common criteria to join the rows on.

Also, to be on the safe side (independent of any language or regional settings), I would always use the ISO 8601 date format - YYYYMMDD - it works in all settings and doesn't suddenly fall over if your server has a different language setting..