CODEWITHSUNDEEP
sql-server-2008
Ajay
Ajay

Reputation: 6590

Not getting Record Between two dates

I want to get rows between 2 dates

Here is the query:

select 
    distinct Convert(varchar(09), [DateTime], 103)
from 
    StudentAttendance_FK 
where 
    LecturerID = 5033 and 
    CourseID = 1004 and 
    SubjectID = 120 and 
    [DateTime] Between '3/8/2012' and '3/11/2012'

Format (MM/DD/YYYY)

Sample values

3/8/2012 11:40:46 PM
3/8/2012 11:40:46 PM
3/9/2012 11:57:55 AM
3/9/2012 10:48:02 PM
3/10/2012 11:57:20 PM

The query isn't returning any rows between these two dates. Any changes in this query?

thanks you

Ajay.

Upvotes: 0

Views: 378

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

If you're using 103 (d/m/y) then why is your where clause using m/d/y? Are you using British/Canadian regional settings or not? Stick to standard, unambiguous formats such as yyyymmdd and you will have much more predictable results.

SELECT DISTINCT CONVERT(DATE, [DateTime]) 
FROM dbo.StudentAttendance_FK
WHERE LecturerID = 5033
AND CourseID = 1004
AND SubjectID = 120
AND [DateTime] >= '20120308' AND [DateTime] < '20120312';

Upvotes: 4

Related Questions