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.
SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...
Should do what you need.
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
DATE type wasn't available before SQL Server 2008.
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.
date1 and date2; that is, the number of midnights you'd pass through to get from date1 to date2
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"
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
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 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
