Search code examples
sqlsql-servert-sql

Calculating orders placed on the end of the month

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 
FROM Sales.Orders  
WHERE orderdate = DATEADD( month, DATEDIFF( month, '18991231', orderdate), '18991231');

The author's explanation is:

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 target month.

However, I'm still a bit confused as to how it actually works. Would someone kindly explain it?

Solution

  • That seems like a rather arcane way to do this. What the code is 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.