Tony Stark Tony Stark - 1 year ago 58
SQL Question

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, im still a bit confused as to how it actually works. could someone please kindly explain it to me? thanks in advance for any help!

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download