Ignore Time When Joining Tables On Dates 1

[Auguy]

SQL 2000. I'm trying to join two tables on the smalldatetime columns in each. One is my date table that does not have a time included in the date and the other table has a time included in it's date. I want to join only on the date portion of the second table. I'm trying to do a sum on the day's total dollars and don't care what time the value was posted. I can use the following code to eliminate the time portion and then do the join, but is this the best way and does it interfere with SQL using any indexes on the dates? These are fairly small tables but I still want to do it the best way.

dateadd(day,datediff(day,0,postdate),0)

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

This is probably the best way to accomplish what you want.

With this method, indexes will probably not be used, especially in SQL2000. Somewhere along the way, SQL Server can recognize that you are stripping the time off a datetime and will allow indexes to be used, but I am fairly certain this is not the case with SQL2000.

Of course, bear in mind that you did not show the entire query. You mentioned a sum on the day's total amount. If you have a filter on query (where clause) that narrows the data down to a single day (or range of dates), then an index would get used. For example:

TableA
Inner Join TableB
  On TableA.DateOnlyColumn = dateadd(day,datediff(day,0,TableB.postdate),0) 
  And TableB.postdate >= '20130918'
  And TableB.postdate < '20130919'

The additional join criteria would allow an index on TableB.postdate to get used.

There are ways to speed things up. Specifically, you could add a computed column and then put an index on the computed column. The index on the computed column would allow the query to run faster. This method would cause another index to be created. Indexes take space in the database as well as overhead for inserts, updates, and deletes.

For example...

Alter Table TableB Add PostDate_DateOnly As (dateadd(day,datediff(day,0,postdate),0))
Create Index idx_TableB_PostDate_DateOnly on TableB(PostDate_DateOnly)

With this method, your join would look like this...

TableA
Inner Join TableB
  On TableA.DateOnlyColumn = TableB.postdate_dateOnly

Personally, if this were my project, I would try the first method (where you don't add the computed column and index) to see if performance is acceptable. If it is, then case closed. In fact, I would only recommend the computed column/index method if you run in to performance problems.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Thanks George. I have a filter on my date table that will be limited to the last 12 days, weeks, months, or quarters. I'm using this data as the source for a chart in a .net program and allowing the user to choose the time frame and various other filters like job type, customer, etc. I will check the performance using the first method but I don't think it will be a problem with these small files.

Auguy
Sylvania/Toledo Ohio