How to Compare SQL SmallDateTime Fields for Equality

It is meaningless to compare for equality (==) two fields of type SmallDateTime, because they contain time component and they are hardly ever the same.     Here are some interesting ways around this problem to check, if two SmallDateTime fields have the same date component.

-01- Use BETWEEN
SELECT * FROM tTimeSheet
WHERE Date BETWEEN ’08/4/11′ and ’08/5/11′

-02- Use Date Portion of the date
SELECT * FROM tTimeSheet
WHERE CONVERT(CHAR(10),Date,120) = ‘2011-08-04’

-03- Use DATEPART
SELECT * FROM tTimeSheet
WHERE
DATEPART(YEAR, Date) = ‘2011’ AND
DATEPART(MONTH,Date) = ’08’ AND
DATEPART(DAY, Date) = ’04’

-04- Use Rounding
SELECT * FROM tTimeSheet
WHERE
CAST(FLOOR(CAST(Date AS FLOAT))AS DATETIME) = ‘2011-08-04’

-05- Use LIKE
SELECT * FROM tTimeSheet
WHERE Date LIKE ‘Aug  4 2011%’

*Note two spaces between month and date above: Aug__4.

(Visited 106 times, 2 visits today)

1 Comment

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *