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
1 comment:
Here is another way to trim the time portion of DateTime fields. This is faster than converting to character and back:
DATEADD(day, DATEDIFF(day, 0, CREATED_ON),0) <= DATEADD(day, DATEDIFF(day, 0, @dReportDate),0)
Also, the same method can be used to get dates within a range, for example all dates that are in the current month:
CREATED_ON >= DATEADD(month, DATEDIFF(month, 0, getdate()), 0) AND
CREATED_ON < DATEADD(month, DATEDIFF(month, 0, getdate()) + 1, 0)
Post a Comment