L' Eomot Inversé (1/21/2013)
Hugo Kornelis (1/21/2013)
... But in SQL Server 2012, there are definitely a lot of new function that are totally useless - except if you want to enable an easier transition from VBA and Access SQL to T-SQL. EOMONTH is one of them, IIF is a very clear example, and I think there were a few more, but I don't have the time to hunt them all down now.I take a different view of Eomonth: anything that makes the developer's life easier is a useful function. It is a lot easier to write EOMONTH(somedate)
for example than to write cast(DATEADD(DAY,-DATEPART(DAY,somedate),DATEADD(MONTH,1,somedate)) AS DATE).
A developer is unlikely to get an EOMONTH call wrong, and you can't say the same for a construction of the same function from datepart and dateadd. It's also a lot easier to read the Eomonth call, to see instantly what it means, and hence to debug and maintain and enhance - so there is a lot of real business value in that function.
It would have saved me some pain if my people could have used something like EOMONTH back in the days of SQL Server 2000. And since we were a shop where the scripting language was JScript not VBA that wasn't because people were converting anything which already used an EOMONTH function.
Maybe some of the other functions are a bit useless, though.
My main issue with EOMONTH is that it's a lost opportunity to do something *really* useful, and a whole bunch of future forum questions.
It's just too compelling to write "WHERE MyDateColumn BETWEEN *something to calculate the first day of the current month* AND EOMONTH(getdate())" - but that would in many cases not return the correct results. EOMONTH always returns the last day of the month with time 0:00, so if my data stores time as well, I lose all events on the last day. For regular datetime data, I would want a function to return 23:59:59.997; for smalldatetime 23:59; for datetime2(7) 23:59:59.9999999, and for my specific data that is constrained to store only whole hours I want 23:00.
So for me, a far better choice would have been to implement a BOMONTH (for the first day of the month - though one can wonder how much this simplifies life, as the first of the month is pretty easy to get anyway) and then use a DATEDIFF to subtract 3ms, 1 second, 100 ns, or an hour.