Why wont my data filter the date .. smalldatetime

SELECT S.stu开发者_StackOverflow中文版dentID, S.studentFName, S.DOB, S.studentEMail, S.studentAddress
FROM Student AS S RIGHT OUTER JOIN
(SELECT Attendance.studentID, COUNT(*) AS cnt FROM Attendance CROSS JOIN
CourseUnit WHERE (Attendance.attStatus = 'Yes') AND (CourseUnit.courseCode = 'S3000') AND (CONVERT(VARCHAR, Attendance.attDate, 101) < '11/10/2010') GROUP BY Attendance.studentID
HAVING (COUNT(*) < 5)) AS A ON A.studentID = S.studentID

Everything works, except the attDate. I dont have any records in 2010, and it still brings up all of them ??

Let me guess - your dates are a varchar? Fired. Any reason for that ugly ugly ugly unperformant and error prone construct

(CONVERT(VARCHAR, Attendance.attDate, 101) < '11/10/2010')

instead of Attendance.attDate being a Date object to start with? Try that and see whether that works.

Aren't you saying 'give me all dates less than this day in 2010' (< '2/10/2010')?

Maybe you need to switch around your < to a >.

Attendance.attDate should be of data type datetime and the comparison should be Attendance.attDate < '20101110' (YYYYMMDD) if you want the rows prior to the given date value.