CODEWITHSUNDEEP
sqlsql-server
Mcol
Mcol

Reputation: 239

Dateadd and datediff get last day in month

I don't understand why this query do the trick for february every year but not for the months with 31 days? I get 30 days for every months except february. Can Anyone explain it to me?

SELECT  dateadd( mm, -240 + 22, DATEADD(mm, 1 + DATEDIFF(mm, 0, GETDATE()), -1)) AS n

I use the code above cause I need to put in a number in the place for 22 to get different dates. I use it like a calender function with the last date for every month.

Upvotes: 0

Views: 2719

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239744

The simplest variant is this:

DATEADD(month,DATEDIFF(month,'20010101',GETDATE()),'20010131')

Which basically says DATEDIFF(month,'20010101',GETDATE()) - how many (whole) months have elapsed since January 2001? And then adds that whole number of months onto the 31st January 2001. But when adding months, DATEADD will truncate the days portion if no such day exists in that month - so 31 gets rounded down to 30, 29 or 28, as appropriate.

The two dates picked above are arbitrary - all we needed to use was any month with 31 days in it.

If you need to vary which month you get (rather than the current month), you should be able to adjust this value just by adding or subtracting values here:

DATEADD(month,DATEDIFF(month,'20010101',GETDATE()) + N,'20010131')

Where N allows you to adjust which month is interesting to you (relative to the current month)

This code:

DATEADD(mm, 1 + DATEDIFF(mm, 0, GETDATE()), -1)

In the original question is another way of obtaining "the last day of the current month"1 - and gets the same rounding behaviour described above. So if you run that code in April (today) it's get 30th April. If you then apply a further DATEADD() operation to that date, as in the question, then it can never restore itself back to the 31st.

If you use the original question code in February, it'll behave even worse. So a key aspect to getting any code like this to work correctly is to only use DATEADD once, to get to the final correct month, and where the rounding behaviour will actually be to your benefit.

1In my opinion, less readably so. I don't understand why people are such fans of the shortened date portion specifiers, nor why they'd prefer to use magic numbers such as the final -1 actually meaning 31st December 1899.

Upvotes: 1

Dzmitry Sauchanka
Dzmitry Sauchanka

Reputation: 412

I usually do it the following way.

First, get the first day of the current month:

SELECT CAST(DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()) AS DATE)

Then, get the first day of the next month adding a month to previously obtained date:

SELECT CAST(DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE())) AS DATE)

Finally, get the last day of current month as the day before the first day of the next month:

SELECT CAST(DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(GETDATE()), GETDATE()))) AS DATE)

Upvotes: 0

gvee
gvee

Reputation: 17171

SELECT Current_Timestamp As today
     , DateAdd(mm, DateDiff(mm, 0, Current_Timestamp), 0) As first_of_current_month
     , DateAdd(mm, DateDiff(mm, 0, Current_Timestamp) + 1, 0) As first_of_next_month
     , DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, Current_Timestamp) + 1, 0)) As last_of_current_month
;

Upvotes: 0

Related Questions