SQL BETWEEN

[RosieGp]

I have this query to return records between this date to that date as following:

Select * 
from Employee
where Employee_ID = 1
and convert(varchar(10), DateofVAcation,101)
between '12/01/2010' and '01/01/2011'

This won't return anything.

If the data in Between clause is of the same year, Say for instance: '12/01/2010' and '12/31/2010 then it returns data or 01/01/2011 and 01/31/2011.
But different years won't. Please help , i don't know why it is happening.
Thanks.

 

[SgtJarrow]

Why the Convert to varchar?? Is your DateOfVacation not a DateTime?

Try removing the Convert...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[RosieGp]

It is datetime. I changed my query to smalldatetime. it works.
Thanks.

 

[markros]

Take a look at these two bogs

The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library