CODEWITHSUNDEEP
sqlsql-servert-sql
Thor
Thor

Reputation: 10068

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?

Upvotes: 1

Views: 1178

Answers (4)

Beckie
Beckie

Reputation: 1

If orderdate is a DATETIME field:

WHERE EOMONTH(orderdate) = CAST(orderdate as date)

If orderdate is not a DATETIME field:

WHERE EOMONTH(orderdate) = orderdate

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270643

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.

Upvotes: 3

DimaSUN
DimaSUN

Reputation: 921

select DATEDIFF(MONTH, '20160131', '20160201')

give us 1 month and

SELECT DATEADD(month, 1, '20160131')

give us 2016-02-29 00:00:00.000

that's ok

Upvotes: 1

Thor
Thor

Reputation: 10068

I tried out the query myself and seem to have got the hang of it. here is what i wrote just in case anyone else is interested

SELECT DATEADD(month, DATEDIFF(MONTH, '20160131', '20160201'), '20160131');

result: 

2016-02-29 00:00:00.000

so my interpretation is that adding one or more "month" to a particular date in which the last date of the month is 31 will always return a date in which the date is the last day of the month. if this sentence makes any sense...

Upvotes: 0

Related Questions