SQL Server Date/Time Difference

[obrienkev]

Hi all,

The below query should return one result but it returns nothing. The dataes in the database are...

class_start: 04/07/2006 10:00:00
class_end: 04/07/2006 13:00:00

Any ideas why a result isn't returned? Thanks.

SELECT     *
FROM         class cl INNER JOIN
                      course c ON cl.course_ID = c.course_ID INNER JOIN
                      location l ON cl.location_ID = l.location_ID INNER JOIN
                      instructor i ON cl.instructor_ID = i.instructor_ID INNER JOIN
                      training tr ON tr.class_ID = cl.class_ID
WHERE     (cl.class_start >= DATEADD(d, DATEDIFF(d, 0, '2005-01-01'), 0)) AND (cl.class_end <= DATEADD(d, DATEDIFF(d, 0, '2006-08-30'), 0)) AND
                      (cl.course_ID = '1')
ORDER BY cl.class_start DESC

[liquorvicar]

Not sure what your SQL statement is doing with the dates, it all looks abit long-winded. Why not just:

 SELECT     *
FROM         class cl INNER JOIN
                      course c ON cl.course_ID = c.course_ID INNER JOIN
                      location l ON cl.location_ID = l.location_ID INNER JOIN
                      instructor i ON cl.instructor_ID = i.instructor_ID INNER JOIN
                      training tr ON tr.class_ID = cl.class_ID
WHERE     cl.class_start >= '2005-01-01' AND cl.class_end <  '2006-08-31' 
ORDER BY cl.class_start DESC