SQL BETWEEN Operator

Question

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,ClosedDate) AS DiffDate,
  FROM mytable
 WHERE (CONVERT(varchar, EventDate, 101) BETWEEN '04/01/2010' AND '04/30/2010') 

Answer 1

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.

Answer 2

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.

Answer 3

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)

Answer 4

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