SQL Server date compare ignores year

Question

I am running a query on SQL Server 2005 to look up records within a date range. The WHERE clause looks like this:

WHERE (CONVERT(NVARCHAR, fldPublishedDate, 101) >= '" & toDate & 
   "' AND CONVERT(NVARCHAR, fldPublishedDate, 101) <= '" & fromDate & "')"

toDate and fromDate are entered like this 04/01/2005 and 04/30/2005.

The results I am getting are definitely all between 1st and 30th of April but from various years, ranging from 2005 (when data collection started) to 2012. It looks like the year is simply ignored from the comparison. Am I missing something here?

Thank you for taking your time to read this.

Answer 1

Why are you converting to a string? Of course this doesn't work as expected. You're saying:

IF '12/02/2010' > '09/12/2012'

Which of course yields true. You should be saying:

WHERE fldPublishedDate >= '20050401'
  AND fldPublishedDate < DATEADD(DAY, 1, '20050430');

You don't need to convert the column to a date only if you're only comparing date ranges. Most importantly you should be passing in dates in an unambiguous format that is immune from regional and language settings (e.g. YYYYMMDD). 101 isn't a safe style because what month is 04/06/2012 for people in England? That's June, not April. I suspect you should be formatting these dates better in your (classic ASP?) code, or passing in proper datetime data types from whatever language you're using. Finally, don't use NVARCHAR without length:

• Bad habits to kick : declaring VARCHAR without (length)

Answer 2

Assuming fldPublishedDate is a datetime, and that you want to compare on date only.

In SQL 2008, use the handy-dandy date type:

WHERE CONVERT(date, fldPublishedDate) >= CONVERT(date, '" & toDate & 
"' AND CONVERT(date, fldPublishedDate) <= CONVERT(date, '" & fromDate & "'"

In < SQL 2008, you can use the crazy syntax:

WHERE DATEADD(dd, DATEDIFF(dd, 0, fldPublishedDate), 0) >= CONVERT(datetime, '" & toDate & 
"' AND DATEADD(dd, DATEDIFF(dd, 0, fldPublishedDate), 0) <= CONVERT(datetime, '" & fromDate & "'"

Tehcnically, SQL will convert your datetime strings for you - so you could leave off the conversion of your values.

Also, you should readup on SQL Injection and parameters - it's easier to write, easier to read, and safer as well. Win, win, win.