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

x
?
Solved

Alternatives to Datediff

Posted on 2006-05-30
10
Medium Priority
?
2,571 Views
Last Modified: 2008-03-03
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
Comment
Question by:halifaxman
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
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

by:kenpem
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

by:Anthony Perkins
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 75

Expert Comment

by:Anthony Perkins
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

by:anyoneis
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

by:Lowfatspread
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

by:Anthony Perkins
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

by:Anthony Perkins
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

by:kenpem
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

by:Anthony Perkins
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question