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