Compare dates using SQL Server

Question

I have two dates (datetimes):

date1 = 2010-12-31 15:13:48.593
date2 = 2010-12-31 00:00:00.000

It's the same day, just different times. Comparing date1 and date2 using <= doesn’t work because of the date1 time. So date1 <= date2 is wrong, but it should be true. Can I compare them by just looking at the year, month and day so they are the same? It's SQL Server 2008.

What version of SQL Server? In what context are you doing this comparison (if you are comparing against columns you need to take care to keep things sargable)? — Martin Smith, Jan 25 '11 at 13:28
I am doing this in a "select case". If the dates are <= do this, if not, do that. Its SQL Server 2008 — grady, Jan 25 '11 at 13:30
For the comparison to fail, it seems that your date are stored as strings. If they were stored as datetime, I guess the comparison — pascal, Jan 25 '11 at 13:35

score 106 · Accepted Answer · edited Jun 20 '20 at 09:12

106

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 25 '11 at 13:31

Martin Smith

438,706
87
741
845

1

Note that the `DATE` type wasn't available before SQL Server 2008. – LukeH Jan 25 '11 at 13:33
@Luke - Yep. Hence my query as to what version the OP is on. – Martin Smith Jan 25 '11 at 13:35
@grady - Yes it does! See test case this does exactly what you say you want! – Martin Smith Jan 25 '11 at 13:42

LukeH · Answer 2 · 2011-01-25T13:49:13.623

74

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.

edited Jan 25 '11 at 13:49

answered Jan 25 '11 at 13:29

LukeH

263,068
57
365
409

This works, but can you pls explain why? Isnt that just comparing days? – grady Jan 25 '11 at 13:34
1

@grady: Nope it counts the number of day *boundaries* between `date1` and `date2`; that is, the number of midnights you'd pass through to get from `date1` to `date2` – LukeH Jan 25 '11 at 13:40
5

+1 Most elegant solution here. I'd consider ABS or <> 0 though if needed to allow for date2 before date1 – gbn Jan 25 '11 at 13:53

score 4 · Answer 3 · answered Oct 31 '14 at 21:32

The simple one line solution is

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')=0

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')<=1

datediff(dd,'2010-12-31 15:13:48.593','2010-12-31 00:00:00.000')>=1

You can try various option with this other than "dd"

score 1 · Answer 4 · answered Apr 05 '17 at 07:06

I am always used DateDiff(day,date1,date2) to compare two date.

Checkout following example. Just copy that and run in Ms sql server. Also, try with change date by 31 dec to 30 dec and check result

BEGIN

declare @firstDate datetime
declare @secondDate datetime


declare @chkDay int

set @firstDate ='2010-12-31 15:13:48.593'
set @secondDate ='2010-12-31 00:00:00.000'

set @chkDay=Datediff(day,@firstDate ,@secondDate )

if @chkDay=0
    Begin
        Print 'Date is Same'
    end
else
    Begin
        Print 'Date is not Same'
    end
End

score 0 · Answer 5 · edited Feb 19 '16 at 18:39

Try This:

BEGIN

declare @Date1 datetime
declare @Date2 datetime

declare @chkYear int
declare @chkMonth int
declare @chkDay int
declare @chkHour int
declare @chkMinute int
declare @chkSecond int
declare @chkMiliSecond int

set @Date1='2010-12-31 15:13:48.593'
set @Date2='2010-12-31 00:00:00.000'

set @chkYear=datediff(yyyy,@Date1,@Date2)
set @chkMonth=datediff(mm,@Date1,@Date2)
set @chkDay=datediff(dd,@Date1,@Date2)
set @chkHour=datediff(hh,@Date1,@Date2)
set @chkMinute=datediff(mi,@Date1,@Date2)
set @chkSecond=datediff(ss,@Date1,@Date2)
set @chkMiliSecond=datediff(ms,@Date1,@Date2)

if @chkYear=0 AND @chkMonth=0 AND @chkDay=0 AND @chkHour=0 AND @chkMinute=0 AND @chkSecond=0 AND @chkMiliSecond=0
    Begin
        Print 'Both Date is Same'
    end
else
    Begin
        Print 'Both Date is not Same'
    end
End

score 0 · Answer 6 · edited Feb 19 '23 at 15:17

0

I use the below script in my SQL and it is working fine with me:

SELECT
    ID ,
    CASE
        WHEN DATEDIFF(day, ExpectedDate, ActualDate) < 0
        THEN 'Late'
        ELSE 'OnTime'
    END shipmentStatus
FROM orders
ORDER BY ExpectedDate

edited Feb 19 '23 at 15:17

Peter Mortensen

30,738
21
105
131

answered Oct 11 '21 at 21:00

Ali

1,080
16
22

Compare dates using SQL Server

6 Answers6

Test Case

Linked