DateTime Efficiency and Best Practices Question

Posted on 2008-01-29
Medium Priority
Last Modified: 2008-01-30
I use this convert, float, and floor things a lot, and I'll admit that sql is probably not my strongest skill.  It seems like I remember starting doing this when I got invalid results, but someone told me that it was very inefficient, and I do a lot of date/time based queries in this app, and I really need to get it right.  

Here are some examples:
-- Gets it based on certain dates... say all orders from January 10th to 13th
Select avg(datediff(second,w.dispBeginTime,w.dispFinishTime))
  From WorkOrder w
  Where      w.keyCompany = @keyCompany
      and      w.dispBeginTime  is not null
      and w.dispFinishTime is not null
      and floor(convert(float,w.dispFinishTime)) >= floor(convert(float,@BeginDate))
      and floor(convert(float,w.dispFinishTime)) <= floor(convert(float,@EndDate))

-- Gets it based on certain times... say all orders from January 10th 8:01:01 to January 13th 8:02:02
Select avg(datediff(second,w.dispBeginTime,w.dispFinishTime))
  From WorkOrder w
  Where      w.keyCompany = @keyCompany
      and      w.dispBeginTime  is not null
      and w.dispFinishTime is not null
      and convert(float,w.dispFinishTime) >= convert(float,@BeginDate)
      and convert(float,w.dispFinishTime) <= convert(float,@EndDate)

Thanks, Tony
Question by:picsnet
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 20774199
Try it this way:

Select @BeginDate = CAST(CONVERT(char(8), @BeginDate, 112) as datetime),      -- Or smalldatetime
          @EndDate = CAST(CONVERT(char(8), @EndDate, 112) as datetime)

Select ...
Where ...

 and CONVERT(char(8), w.dispFinishTime, 112) BETWEEN @BeginDate And @EndDate

It is still not going to be able to take advantage of any index, but it should be better.
LVL 18

Expert Comment

ID: 20775182
@acperkins: Have you made any tests? I did some test yesterday (I tried three methods of cutting off the time: with CONVERT, with FLOOR, and  with DATEADD functions). And it seems to me like their performance is very similar (or even the same). But if you have any test results available or maybe you can provide some link to some reliable results of someone else I would be grateful.

Author Comment

ID: 20777917
i did something similar where i used the between, but i kept my dates in floats....

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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 ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

601 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