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 :)
46 Answers
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 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.
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 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 
