This is unusual

[Janaka]

SELECT A.referenceno,  B.datetime
FROM defectiveitem AS A, exportorder AS B, customer AS C 
WHERE A.exportorderid = B.id AND A.customerid = C.id AND C.name = 'SLT' 
AND 
(B.datetime >= '20/04/2001' AND B.datetime <= '21/04/2001') 
ORDER BY B.id

When I run this query I don’t get the dates that are equal to '21/04/2001' even though there are data like that



i am using sql server 2000

[Si_the_geek]

you are using UK date format (dd/mm/yyyy), try using american format instead (mm/dd/yyyy), eg:

SELECT A.referenceno,  B.datetime
FROM defectiveitem AS A, exportorder AS B, customer AS C 
WHERE A.exportorderid = B.id AND A.customerid = C.id AND C.name = 'SLT' 
AND 
(B.datetime >= '04/20/2001' AND B.datetime <= '04/21/2001') 
ORDER BY B.id

[Janaka]

No Luck

[Janaka]

i do get some of the dates 19,20 etc only 21 i don't get

[Si_the_geek]

Ah!! just realised it is a date-time field!!

when you don't specify a time it assumes midnight, so just set the parameters like this:

(B.datetime >= '04/20/2001' AND B.datetime < '04/22/2001')