Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.