Alternatives to Datediff

Datediff, seems to slow the queries I write down i.e.

Datediff(s,field1,field2)


Is there another more efficient way to calculate date and time differences in a query
halifaxmanAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
any function will "slow" down the execution of a query....

can you explain which version of sql server you are using and a common example of how you
are trying to use datediff...
0
 
kenpemCommented:
If you just want to know the number of days between date, simply subtracting them will do it VERY quickly.

SELECT Date1, Date2, (CAST(Date1 AS DECIMAL) - CAST(Date2 AS Decimal)) AS DaysDifference ...

0
 
Anthony PerkinsCommented:
>>Is there another more efficient way to calculate date and time differences in a query<<
Yes.  Try avoiding using a function on the right hand side of WHERE clause.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Anthony PerkinsCommented:
>>Try avoiding using a function on the right hand side of WHERE clause.<<
And I obviously do not know my right from my left.  It should read:
"Try avoiding using a function on the LEFT hand side of WHERE clause."
0
 
anyoneisCommented:
acperkins wrote:

>"Try avoiding using a function on the LEFT hand side of WHERE clause."

So, are saying that:

     WHERE 30 < Datediff(s,field1,field2)

Is more performance than:

     WHERE Datediff(s,field1,field2) > 30

It seems like the optimizer would be able to figure out something that simple?!?!?

David

0
 
LowfatspreadCommented:
yeah but

where field2 > dateadd(s,30,field1)    is probably simpler...

and why is that an easy transform?
0
 
Anthony PerkinsCommented:
>>It seems like the optimizer would be able to figure out something that simple?!?!?<<
It will figure it out just fine, you don't have to worry about that.  It will just result in a table scan and any index will be totally ignored.
0
 
Anthony PerkinsCommented:
In any case, neither of the two options are much help.  Lowfatspread has given you the right solution.
0
 
kenpemCommented:
If you are just worried about things that happened more than thirty seconds apart, a fast solution might be to pre-calculate that 30 seconds as a DATETIME, thus preventing DATEADDs happening all the time:

-- at the top of the script
DECLARE @Secs30 DATETIME, @TD1 DATETIME, @TD2 DATETIME
SET @TD1='01-01-2001 00:00:00'
SET @TD2='01-01-2001 00:00:30'
SET @Secs30=(@TD2 - @TD1)

-- in the comparison
WHERE (field2 - field1) < @Secs30

Now all you're asking the WHERE clause to do is a simple numeric subtraction and compare, which has GOT to be quicker that DATEADD and DATEDIFF, which need to do all sorts of clever inter-type conversion things.
0
 
Anthony PerkinsCommented:
>>which has GOT to be quicker that DATEADD and DATEDIFF, which need to do all sorts of clever inter-type conversion things.<<
Actually, in general no.  If the columns are indexed then there is no way that it can be used and it will have to resort to a table scan.  But don't take my word for it, take a look at the Plan Execution and see for yourself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.