BETWEEN DATES SQL Server 2008

When dealing with dates, remember there is not such field as date in SQL Server 2005/2008. There is datetime field.

For reference there is a date type in MySQL which I think is very useful.

There could be problems when you use between dates in SQL Server 2008. The problem comes because datetime has time information also and that give a new meaning when you are doing comparision.

Lets get the current date.

select getdate()

2012-04-10 17:21:12.090  -- output

If I do

select getdate() as today where GETDATE()='2012-4-10'

-- I get zero result

This is because when comparing dates, sql server 2008/2005 compares time information also. if the time information is 00:00:00, in that case it will print the date but that is not the case. Therefore it does not finds a match.

Using between with dates

Keep this information, if there is no time information, everything will be simple but if there is time information, it gives a new meaning to the day.