[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Alternatives to Datediff

Posted on 2006-05-30
Medium Priority
2,571 Views
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
0
Question by:halifaxman
• 5
• 2
• 2
• +1

LVL 50

Accepted Solution

ID: 16794029
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

LVL 4

Expert Comment

ID: 16794358
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

LVL 75

Expert Comment

ID: 16794943
>>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

LVL 75

Expert Comment

ID: 16794986
>>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

LVL 11

Expert Comment

ID: 16795813
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

LVL 50

Expert Comment

ID: 16797611
yeah but

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

and why is that an easy transform?
0

LVL 75

Expert Comment

ID: 16798175
>>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

LVL 75

Expert Comment

ID: 16798196
In any case, neither of the two options are much help.  Lowfatspread has given you the right solution.
0

LVL 4

Expert Comment

ID: 16799781
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

LVL 75

Expert Comment

ID: 16802107
>>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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  āCannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi fileā error ?