MS SQL - Using DateTime as Date to compare dates

In MS SQL Server we don’t have a Date type. DateTime is using instead. When we had to compare dates that have also time part we can use one of these tricks:

1.Using DateTime as Date to compare dates

To compare 2 dates in SQL - convert them to date first. Use CONVERT to CHAR(10) and date format "101".

Example:

CONVERT(DATETIME, CONVERT(CHAR(10), CREATED_ON, 101)) <= CONVERT(DATETIME, CONVERT(CHAR(10), @dReportDate, 101))

2.Using DATEDIFF to compare dates (only if equal)

DATEDIFF function cannot be used for greater that or less that comparison because it returns only positive numbers but it can be used for checking if dates are equal.

Example:

DATEDIFF(d,@DATE1,@DATE2) = 0

when DATE1 = DATE2