MS SQL compare dates?

Question

I have 2 dates (datetimes):

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

On the same day, only different times. Comparing date1 and date2 using <= does not work due to date1. Therefore, date1 <= date2 is incorrect, but must be true. Can I compare them just by looking at the year, month, and day so that they are the same? Its SQL Server 2008.

Thank:)

Answer 1

SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ... 

Must 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 YN 

Answer 2

Use the DATEDIFF function with the date of the day part.

 SELECT ... FROM ... WHERE DATEDIFF(day, date1, date2) >= 0 

Note that if you want to check that date1 <= date2 , you need to check that DATEDIFF(day, date1, date2) >= 0 , or, alternatively, you can test DATEDIFF(day, date2, date1) <= 0 .

Answer 3

Simple single line solution

 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 options with this parameter, except for "dd"

Answer 4

I always use DateDiff (day, date1, date2) to compare two dates.

Place an order in the following example. Just copy this and run in Ms sql server. Also, try with a change date of 31 dec up to 30 divisions and check the 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 

Answer 5

Try the following:

 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