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 :)
This question is related to
sql-server
tsql
sql-server-2008
date
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
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
.
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
Source: Stackoverflow.com