# 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
###### Who is Participating?

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

Commented:
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

Commented:
>>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

Commented:
>>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

Commented:
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

Commented:
yeah but

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

and why is that an easy transform?
0

Commented:
>>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

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

Commented:
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

Commented:
>>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.