MS SQL compare dates?

97
votes

I have 2 dates (datetimes):

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

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

Thanks :)

5
sql-servertsqlsql-server-2008date
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
I am doing this in a "select case". If the dates are <= do this, if not, do that. Its SQL Server 2008 – grady
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
date1 <= date2 = true? how is 3PM is less than 12AM? – 4 Leave Cover

5 Answers

96
votes
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
73
votes

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.

3
votes

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"

0
votes

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
0
votes

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