Convert SmallDateTime question

I want to return all the records less than a cetain date(MM/dd/yyy). I thought the following query would do that but it does not. The query returns a record for 01/09/2009 which is grater than 11/18/2008.

Select Convert(varchar(10),EndDt,101)
 From Vacation
where Convert(varchar(10),EndDt,101) < '11/16/2008'

This returns 01/02/2009.

I chose to use the convert function in the where because I want to ignore hour, minutes and seconds. Would someone please explain why this query returns a record which seems to be incorrect.
thanks,
pat
mpdillonAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
all dates <= 12/1/2008


select * from tablename
where datefield <= convert(varchar(10), '12/1/2008', 101)
0
 
chapmandewCommented:
Select Convert(varchar(10),EndDt,101)
 From Vacation
where EndDt < '11/16/2008'
0
 
mpdillonAuthor Commented:
Ok, I think I understand. The convert function changes the date to a string. And a string that begins with 11/ is greater than a string that begins with 01/.

So then how would should I compare dates?

Select EndDt From Vaction where Enddt <= '12/01/2008' will return 12/01/2008 00:00 but will not return 12/01/2008 01:00. How do I write a Where statement that only compares the date portion of the date and ignores the Hours and minutes?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
Ok, I gotcha.  try this:

Select Convert(varchar(10),EndDt,101)
 From Vacation
where Convert(varchar(10),EndDt,101) < convert(varchar(10), '11/16/2008', 101))
0
 
mpdillonAuthor Commented:
Sorry didn't work for me. I think it is the string comparison. So let me ask the question differently, how do you select all the dates less than or equal to 12/01/2008.
This will work for a smalldatetime Where EndDt <= '12/01/2008 23:59'
if the field were a datetime field I would have to write
where EndDt <= '12/01/2008 23:59:59:99:99'
but there has to be a better way.
0
 
mpdillonAuthor Commented:
Thanks for your perserverance. It works fine. I even tested with 01/01/2009. If any date was going to fail it would be one that lead off with 01/01. But your function returned 11/19/2208 as being less than 01/01/2009. Just what I wanted.
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.