Is there any significant difference between Month(date) and DatePart(month, date)?

Question

MSDN states that both Month(date) and DatePart(month, date) return the exact same value, however is there any difference between the two methods, performance or otherwise?

I'm building some queries that aggregate large sets of data based on month, and sometimes they take a while to run so I'd like to be sure I'm using the most efficient syntax possible.

I'm currently using SQL Server 2005

Answer 1

I just ran several tests on a largish table (12,430,129 rows) in SQL Server 2008r2.

• The field is smalldatetime
• The field is NOT indexed
• I changed the processing order after each run to eliminate page caching issues

Results:

MONTH() - Ran in 6662, 6583, 6661 and 6560 ms. Average runtime 6616.5ms

DATEPART() - Ran in 6520, 6584, 6552, and 6608 ms. Average runtime 6566ms

So, DATEPART() does seem to be marginally faster. However, this is 7 tenths of a percent difference, so it probably won't matter a whole lot.

Answer 2

In my experience, these two functions are identical in performance. But I would suggest two quick tests in your environment:

• check out the estimated query plans for both options (likely to be the same)
• run a simply query repeatedly (using GO 1000 after your SELECT) and time the performance