SQL Between Statement Not Working With New Year

Jan 6

SQL Between Statement Not Working With New Year

So I ran into a little problem with some of my SQL between statements this last week. The new year was causing no data to be shown in some data grids and graphs but only if the between statement included data from 2008. Here is my old between statement and after I will show the fix. It is pretty simple but maybe it will be helpful to someone else.

DOES NOT WORK!!!

WHERE CONVERT(VARCHAR(10), date, 101) BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101)

NEW BETWEEN STATEMENT THAT WORKS

WHERE date BETWEEN CONVERT(VARCHAR(10), #DateField1#, 101) AND CONVERT(VARCHAR(10), #DateField2#, 101)

For some reason the first CONVERT statement did not like having the range between two different years. Hope this saves someone a couple minutes ;)