Question about recent date only comparisons post
-
Recently someone asked about comparing the date only portion of 2 dates so that times are ignored.
The solution supplied was to use something like
where dateadd (d, 0, datediff(d, 0,MyDate1))
= dateadd (d, 0, datediff(d, 0, MyDate2))
I understand why this works but am I missing something about the datediff function or would what I am using not always work? This is what I have been using.
where datediff(d, MyDate1, MyDate2) = 0
Rick
-
I always use BETWEEN and then have a 24 hour day.
For Example
Where (myDate BETWEEN '4/1/2005 00:00:00' AND '4/1/2005 23:59:59')
KC
www.allyson-kas.com -
KC thanks for the input I really appreciate it.
But I guess I am not really looking for how to accomplish this but rather why the person who provided the solution would complicate the use of the datediff function if it was not really necessary. As far as I can see what I am doing accomplishes the same thing in almost exactly the same way but, at least to me, is far less complicated. I was just wondering if I am overlooking something. Something that would come back to haunt me one day.
Rick
-
The only thing I can see is if the two datetime items are less than 24 hrs apart but not on the same day you might have a problem. You could put in a fake record and see if this is the case.
KC
www.allyson-kas.com -
Already tried that. Situation actually exists in my real data.
After playing around I did get it to mess up if I use '2005-04-03 23:59:59.999' it considers this to be the same day as 2005-04-04 instead of 2005-04-03. '2005-04-03 23:59:59.998' works fine. So I think I can live with a 1ms margin of error.
I tested and the same thing happens with the code supplied by the other person that made me question myself. A minor flaw in the datediff function it seems.
Thanks again.
Here is what I tested with in case you're interested.
declare @Date1 datetime
declare @Date2 datetime
set @Date1 = '2005-04-03 23:59:59.998'
set @Date2 = '2005-04-04 00:00:00.000'
--set @Date2 = '2005-04-03 11:59:59.999'
--set @Date2 = '2005-04-02 00:00:00.000'
--set @Date2 = '2005-04-03 00:00:00.000'
--set @Date2 = '2005-04-02 11:59:59.999'
--set @Date2 = '2005-04-03 13:00:00.000'
--set @Date2 = '2005-04-03 23:59:59.999'
select
case datediff(d, @Date1, @Date2)
when 0 then 'Same day' else 'Different day' end as DayCheck
select
case when dateadd (d, 0, datediff(d, 0,@Date1))
= dateadd (d, 0, datediff(d, 0, @Date2))
then 'Same Day' else 'Different day' end as DayCheck
Rick
-
From BOL:
datetime and smalldatetime (T-SQL)
Date and time data types for representing date and time of day.
- datetime
- Date and time data from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds, as shown in the table.
Example Rounded example 01/01/98 23:59:59.999 1998-01-02 12:00:00.000 01/01/98 23:59:59.995, 01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
1998-01-01 23:59:59.997 01/01/98 23:59:59.992, 01/01/98 23:59:59.993,
01/01/98 23:59:59.994
1998-01-01 23:59:59.993 01/01/98 23:59:59.990 or 01/01/98 23:59:59.991
1998-01-01 23:59:59.990
So you will never find a date in the the database with a time value of 23:59:59.999. As you have seen the system will round it (in this case) to the next day.
I have not tested, but I think your simplified method of using datediff works just fine.
Edit: DateDiff() counts the number of date boundries between two dates. When counting 'days' the boundry is midnight (e.g. 4/2/2005 00:00:00.000). So the number of 'days' between 4/1/2005 23:59:59.997 and 4/2/2005 00:00:00.000 is 1.
-
KC,
Where (myDate BETWEEN '4/1/2005 00:00:00' AND '4/1/2005 23:59:59')
Your method will miss dates with times between 23:59:59.003 and 23:59:59.997.
You can try using the convert function (see BOL). Ex:
Convert(varchar(10), mydate, 101) to compare both dates. This will return the month/day/year without the time portion.
-
Thanks ron. I had pretty much decided I was ok but am glad to have a confirmation.
I was playing around with the convert function yesterday while I was testing and there may be an issue if one is not careful. Check out these results. As you say .999 would never exist so it may not be an issue but if one used between with the first statement and between with the second statement the results may vary.
select convert(varchar(10), '2005-04-04 23:59:59.999', 20)
----------
2005-04-04
(1 row(s) affected)
select convert(varchar(10), cast('2005-04-04 23:59:59.999' as datetime), 20)
----------
2005-04-05
(1 row(s) affected)
Rick
-
The only acceptable way to compare dates is something like the following:
WHERE datecol >= @startdate AND datecol < @enddate
See SQL Server MVP Tibor Karaszi's excellent article "The ultimate guide to the datetime datatypes" (http://www.karaszi.com/SQLServer/info_datetime.asp) for all you need to know regarding datetime.
-
The only acceptable way to compare dates is something like the following:
WHERE datecol >= @startdate AND datecol < @enddate
Not quite, I think!
use northwind
select * from orders where orderdate >='19960704' and orderdate <='19960716'
select * from orders where orderdate between '19960704' and '19960716'
Compare the execution plans and watch how the BETWEEN is internally resolved by SQL Server.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Rick,
Yes, a little caution is advised. In you first example
select convert(varchar(10), '2005-04-04 23:59:59.999', 20)
you are converting a string value to varchar(10).
In the second example
select convert(varchar(10), cast('2005-04-04 23:59:59.999' as datetime), 20)
you first cast it to a datetime and there is where it gets rounded up to the next day.
When I suggested using convert I meant for it to be used against existing datetime values.
----------------------------------
Chris thanks for posting the link about datetime datatypes. I will take a look at it.
--ron
-
>select * from orders where orderdate >='19960704' and orderdate select * from orders where orderdate between '19960704' and '19960716'
>Compare the execution plans and watch how the BETWEEN is internally resolved by SQL Server.
Yes, but I did not use <= as the comparison operator for the second clause, I used < alone. Like Tibor describes in the article (didn't you translate that article to German Frank? [
] ) that avoids the problem of the (lack of) precision of the datetime data type. Of course, my statement requeires that the start and end dates are set correctly depending on what values on is looking for. -
Yes, I did the translation. Hm, that reminds me that I finally need to add the additions Tibor made.
I also use generally your syntax with >= and <. It's easier to understand than BETWEEN for the very reason you've mentioned. When you don't use <= but rather < only, the quirky query would look like:
use northwind
select * from orders where orderdate >='19960704' and orderdate <'19960716'
select * from orders where orderdate between '19960704' and '19960715'
which might or might be not intuitive.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Maybe this better illustrates why I said be careful. In this case the string as date is sitting in a table which is not likely (but I have seen it) but just to be thorough.
declare @MyTable table (TrueDate datetime, StringDate varchar(30))
insert into @MyTable (TrueDate, StringDate) values ('2005-04-06 00:00:00.000', '2005-04-06 00:00:00.000')
print 'Show values that are in the table'
select * from @MyTable
Print 'Where clause supplied a date to compare against'
select * from @MyTable where TrueDate between '2005-04-05 00:00:00.000' and '2005-04-05 23:59:59.999'
Print 'Where clause supplied a string to compare against'
select * from @MyTable where StringDate between '2005-04-05 00:00:00.000' and '2005-04-05 23:59:59.999'
Print 'Where clause supplied a string converted to a date to compare against'
select * from @MyTable where cast(StringDate as datetime)between '2005-04-05 00:00:00.000' and '2005-04-05 23:59:59.999'
Thanks all.
Rick
-
Rick,
For more info on your original question check out this theread:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=174762&edit=1
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply