MS SQL compare dates?
Solution 1:
SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...
Should do what you need.
Test Case
WITH dates(date1, date2, date3, date4)
AS (SELECT CAST('20101231 15:13:48.593' AS DATETIME),
CAST('20101231 00:00:00.000' AS DATETIME),
CAST('20101231 15:13:48.593' AS DATETIME),
CAST('20101231 00:00:00.000' AS DATETIME))
SELECT CASE
WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) THEN 'Y'
ELSE 'N'
END AS COMPARISON_WITH_CAST,
CASE
WHEN date3 <= date4 THEN 'Y'
ELSE 'N'
END AS COMPARISON_WITHOUT_CAST
FROM dates
Returns
COMPARISON_WITH_CAST | COMPARISON_WITHOUT_CAST
Y N
Solution 2:
Use the DATEDIFF
function with a datepart of day
.
SELECT ...
FROM ...
WHERE DATEDIFF(day, date1, date2) >= 0
Note that if you want to test that date1
<= date2
then you need to test that DATEDIFF(day, date1, date2) >= 0
, or alternatively you could test DATEDIFF(day, date2, date1) <= 0
.