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.

Accepted Answer

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)

SQL Server date compare ignores year

Solution

Problem