What Happened Today? DATE and Date Ranges Over DATETIME

re: What Happened Today? DATE and Date Ranges Over DATETIME

AaronBertrand — Tue, 20 Oct 2009 19:02:57 GMT

Yeah there was some follow-up in the comments on the optimizations for DATE. I'm not crazy about using the CONVERT() on the LHS just because it is easy to change the code and forget that the CONVERT() is only used because it is an exception to the rule (I'm having deja vu that we have discussed habits here before).

I also suggest you stop using calculations like GETDATE()-1 as this will break if you do a massive search/replace for sysdatetime(). :-)

re: What Happened Today? DATE and Date Ranges Over DATETIME

Dan Rolfe — Tue, 20 Oct 2009 19:11:18 GMT

The rows from today.. a little extra writing but specific. Even the MS-Brain can figure it out logically

<code>

declare @date datetime

set @date = '10/20/2009 23:59:59.999'

SELECT COUNT(*) FROM [TABLE]

WHERE (MONTH(Date_Field) = MONTH(@date) AND YEAR(Date_Field) = YEAR(@date) AND DAY(Date_Field) = DAY(@date))

</code>

re: What Happened Today? DATE and Date Ranges Over DATETIME

Adam Machanic — Tue, 20 Oct 2009 19:20:50 GMT

Sorry, Dan, but that query will force a table scan to occur. Not a good idea.

re: What Happened Today? DATE and Date Ranges Over DATETIME

Adam Machanic — Tue, 20 Oct 2009 19:23:09 GMT

Aaron: Yes, that's why I put the -1 inside the CONVERT :-) -- in this case it was just much easier to read than DATEADD. I know firsthand the pain of the overloads not being supported; I recently did a conversion of a 2005 DB to 2008, and along the way we changed all of the date/time columns in the database from [SMALLDATETIME with a constraint forcing midnight] to DATE. Suddenly lots and lots of code broke all over the place. Oops.

re: What Happened Today? DATE and Date Ranges Over DATETIME

AaronBertrand — Tue, 20 Oct 2009 20:35:26 GMT

Placing the -1 inside the convert doesn't help. This still yields a type clash error when you change GETDATE() to SYSDATETIME(), since it tries to subtract:

CONVERT(DATE, SYSDATETIME()-1)

I understand why you want to skip the DATEADD() syntax to make code less busy, but especially in the context of new DATE/TIME features I think it's dangerous to do so.

re: What Happened Today? DATE and Date Ranges Over DATETIME

AaronBertrand — Tue, 20 Oct 2009 20:35:57 GMT

I think Dan missed the link to my date/range post. :-)

re: What Happened Today? DATE and Date Ranges Over DATETIME

Sankar Reddy — Tue, 20 Oct 2009 23:43:24 GMT

Just want to chime in and add this. In previous versions of SQL Server 2005, a lot of noise was made about datetime data type conversions and I know a lot of code was modified to use someting like below dateadd(dd,datediff(dd,0,getdate()),0). I stumbled across this post by CSS about a performance problem that the above code snippet has inherent problems with low estimates in SQL Server 2005/2008 leading to performance problems. Have any of you ran into this isse?

http://blogs.msdn.com/psssql/archive/2009/07/17/using-datediff-can-query-performance-problems-in-sql-2005-and-2008.aspx

re: What Happened Today? DATE and Date Ranges Over DATETIME

AaronBertrand — Wed, 21 Oct 2009 00:14:08 GMT

I can see where this could cause an issue if your data distribution is heavily skewed, but in a normal distribution the estimate shouldn't affect performance by much at all. I haven't seen the issue yet (though I now know to watch for it). In any case, almost unilaterally when I am performing date range queries, the DATEADD/DATEDIFF calculations aren't actually part of the query; I apply them to the incoming parameter and/or create new parameters, depending on the other functionality in the procedure. The query itself then uses the variable as opposed to a calculation that happens to use the variable. Yes this can lead to parameter sniffing issues but certainly nothing due to the use of DATEADD/DATEDIFF vs. more primitive methods like converting to a string. The big issue is when you essentially perform one or two calculations for every row, and this is certainly going to lead to more frequent, severe and predictable/preventable performance problems than an estimation problem.

re: What Happened Today? DATE and Date Ranges Over DATETIME

Adam Machanic — Wed, 21 Oct 2009 01:10:07 GMT

Sankar: Agreed with Aaron; the potential issue seems like an edge case, not a normal situation. I have used the DATEADD/DATEDIFF techniques in dozens of projects--hundreds of queries--and have never seen any issues caused by estimates. It's definitely good to keep in mind but there are certainly bigger issues to dwell upon!

re: What Happened Today? DATE and Date Ranges Over DATETIME

Sankar Reddy — Wed, 21 Oct 2009 06:30:00 GMT

Neither did I (and/or probably didn't even noticed) and was curious coming from PSS. Thanks for the feedback.

re: What Happened Today? DATE and Date Ranges Over DATETIME

Joe Celko — Sun, 25 Oct 2009 13:49:20 GMT

Wouldn't it be faster to use the constant phi and the closed form?

Fibonacci(@n INTEGER)

RETURN

EOUND (((POWER (1.6190339887, @n)- POWER (1.0 - 1.6190339887, @n))/ SQRT (5.0)), 0);

untested. Probably ought to cast phi as DOUBLE PRECISION

re: What Happened Today? DATE and Date Ranges Over DATETIME

Adam Machanic — Sun, 25 Oct 2009 14:33:42 GMT

Joe: Wrong post. I think you meant to be here:

http://sqlblog.com/blogs/peter_larsson/archive/2009/10/18/simple-fibonacci-calculation.aspx

Not sure what EOUND is, either :-)

re: What Happened Today? DATE and Date Ranges Over DATETIME

matt — Fri, 06 Nov 2009 16:17:58 GMT

wow! that's great! thanks!

Connect Digest : 2010-01-22

Aaron Bertrand — Fri, 22 Jan 2010 20:46:35 GMT

Give us easier to read execution plans Michelle Ufford ( @SQLFool ) recently asked for help pinpointing