CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Yves
Yves

Reputation: 12507

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

Upvotes: 1

Views: 964

Answers (4)

patrick
patrick

Reputation: 1

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

Upvotes: 0

bobs
bobs

Reputation: 22184

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)

Upvotes: 0

Mark Ransom
Mark Ransom

Reputation: 308206

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.

Upvotes: 11

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 5

Related Questions