[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

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
0
mpdillon
Asked:
mpdillon
  • 3
  • 3
1 Solution
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
chapmandewCommented:
all dates <= 12/1/2008


select * from tablename
where datefield <= convert(varchar(10), '12/1/2008', 101)
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now