I'm currently studying sql server using the book Ben-Gan, Itzik. T-SQL Fundamentals. Below is a query used to select order placed at end of the month. (i know that function EOMONTH() can also be used)
SELECT orderid, orderdate, custid, empid
WHERE orderdate = DATEADD( month, DATEDIFF( month, '18991231', orderdate), '18991231');
This expression first calculates the difference in terms of whole
months between an anchor last day of some month (December 31, 1899, in
this case) and the specified date. Call this difference diff. By
adding diff months to the anchor date, you get the last day of the
That seems like a rather arcane way to do this. What the code doing is calculating the number of months since the last day of some month. Then, it adds this number of months to that date. Because of the rules of
dateadd(), the month remains the last date.
However, I prefer a simpler method:
where day(dateadd(day, 1, orderdate)) = 1
I find this much clearer.