<= Date gives odd result

[evaleah]

I am using the following syntax in my WHERE clause:
DateOnStudy <= '08/13/02'

There are two records in the db that fit that. One DateOnStudy = '08/12/02'. The other DateOnStudy = '01/21/02'. But my query is only giving me the first and not the second.

Any ideas about why that is and what I can do about it?

Thanks!
Eva

 

[NoCoolHandle]

What is the datatype of the col?

 

[datuk04]

try this...

WHERE DateOnStudy <=
CONVERT(DATETIME,'2002-08-13 00:00:00',102)

'//-------------------------
' your [DateOnStudy] colums datatype should be DATETIME

good luck.

 

[SonOfEmidec1100]

If the datatypes are datetime or strings then the query should work.

Its almost as if they are character strings in the format dd/mm/yy. However the values you show are after a default conversion to a datetime datatype. In which case your where clause should be in the same format ie DateOnStudy <= '13/08/02'.

Dont know if this helps, I think Ive confused myself.

 

[vidru]

Is it possible that the record where DateOnStudy = '08/12/02' has a time stamp on it? That'd be my guess.

-dave

 

[evaleah]

The column is datetime.

The time stamp on both records is 00:00:00.

Sorry, should have mentioned those items with the initial question.

 

[evaleah]

DOH!!!!!!!!!!

I must have been exhausted last night when I was working on this. The second date isn't 1/12, is 12/1/2002 which any fool knows is NOT <= 8/12.

Thank you everyone for helping. I will try not to post such a ridiculous question again. Wow, I feel silly. I spent tons of time on this last night.

UGH! Just goes to show you overtime doesn't always pay

 

[SQLSister]

Don't feel bad, I think all of us have been totally stumped by something that we later come back to and instantly see what the problem is. I do agree that one becomes less able to think and process information the more overtime one works. I've had times when working overtime that I find myself spending an hour or more on something which would take me 5 minutes or less if I was fresh. When this happens to me, I always stop and go home because I've found that any work I do after I get that tired will need to be redone in the morning. I've never had a supervisor get upset with me when I tell him that's why I'm leaving either.