Mahmoud ElMansy: knowledge meant to be free

DateTime Operators

Using Operators with Date and Time Data Types
The relational operators (<, <=, >, >=, <>), comparison operators (=, <, <=, >, >=, <>, !<, !>) and logical operators and Boolean predicates (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS, and LIKE)
are supported for all the date and time data types.
Possible Usage

USE [AdventureWorks]
SELECT * FROM Person.Contact
WHERE DateDiff(dd, ModifiedDate, '01/01/2004') = 0

SELECT * FROM Person.Contact
WHERE CONVERT(VARCHAR(20), ModifiedDate, 101) = '01/01/2004'

SELECT * FROM Person.Contact
WHERE YEAR(ModifiedDate) = 2004
AND MONTH(ModifiedDate) = 1 AND DAY(ModifiedDate)=1

SELECT * FROM Person.Contact
WHERE ModifiedDate LIKE '01/01/2004%'

SELECT * FROM Person.Contact
WHERE ModifiedDate IS NULL

SELECT * FROM Person.Contact
WHERE ModifiedDate IN('01/01/2004','02/01/2004','03/01/2004')

SELECT * FROM Person.Contact
WHERE ModifiedDate >= '01/01/2004'  AND ModifiedDate < '01/02/2004'

and from my opinion the best practice is using > and < operators i think it is best performance