SQL BETWEEN Operator

Why am I getting '2009' data? What am i doing wrong with the WHERE Clause?

SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE, 
       CONVERT(varchar, ClosedDate, 101) AS CLOSEDDATED, 
       DATEDIFF(Day,EventDate,Closed开发者_StackOverflow社区Date) AS DiffDate,
  FROM mytable
 WHERE (CONVERT(varchar, EventDate, 101) BETWEEN '04/01/2010' AND '04/30/2010') 

You're doing a string comparison, which goes from left to right. '04/10/2009' is between '04/0' and '04/3'.

If the field you're comparing is a DATETIME, don't try to convert it. SQL server can convert the strings to dates and do the comparison properly.

If you use a supported date format, SQL Server will implicitly convert the string to a DATETIME:

SELECT CONVERT(varchar, EventDate, 101) AS EVENTDATE, 
       CONVERT(varchar, ClosedDate, 101) AS CLOSEDDATED, 
       DATEDIFF(Day,EventDate,ClosedDate) AS DiffDate,
  FROM mytable
 WHERE EventDate BETWEEN '2010-04-01' AND '2010-04-30' 

Your query is just doing string comparison, which has no bearing on date spans.

Your WHERE clause may be doing string comparison instead of date comparison. If you want to do a date comparison you can change

CONVERT(varchar, EventDate, 101)

to

CAST (CONVERT(varchar, EventDate, 101) AS DATETIME)

You really don't need all the conversion. The dates from the calendar will have the right start and end times. You also want to consider events that might go past the end date or start before the date and end within the date range. Or finally start before and go past...

Here's some code we use

    (EventStartDtTm >= startDt and EventStartDtTm <= endDt) 
 || (EventStartDtTm <= startDt and EventEndDtTm >= startDt)

-- patrick