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 889 times, 3 visits today)

1 Comment

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