微软 SQL 比较日期？

我有两个约会(日期时间) :

Date1 = 2010-12-3115:13:48.593 < br > < em > date2 = 2010-12-3100.00.00.000

同一天，只是时间不同。使用 < = 比较 date1和 date2不起作用，因为 date1时间。所以 date1 < = date2是错误的，但应该为真。我可以通过看年份、月份和日期来比较它们吗? 因此它们是一样的？它的 SQLServer2008。

谢谢:)

247053 次浏览
小开

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.

小开
最佳答案 
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
小开

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"

小开

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
小开

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
小开

it is never too late 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

提交答案