last day of the month minus 1 day

Question

last day of the month minus 1 day

smoham22

Mr or Mrs. 500

Points: 525
More actions
October 31, 2019 at 3:11 am

#3693359

I am trying to get last day of the month minus one day. I am using belows query to get lasy day of the month. How can I get Last day of the month -1 day

select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

So, you already have the last day of the month. Just subtract 1 day and you'll have the second last day

select DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))

I am not feeling -1 as the base date, so using 0 ...

select DATEADD(DAY, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

How to post data/code on a forum to get the best help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT

[LastDayOfThisMonth] = EOMONTH(GETDATE()),

[SecondLastDayOfThisMonth] = DATEADD(DAY,-1,EOMONTH(GETDATE()))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

ChrisM@Work wrote:

SELECT
[LastDayOfThisMonth] = EOMONTH(GETDATE()),
[SecondLastDayOfThisMonth] = DATEADD(DAY,-1,EOMONTH(GETDATE()))

I think EOMONTH was introduced in SQL 2012 and this is the SQL 2008 board.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Jonathan AC Roberts wrote:

ChrisM@Work wrote:
SELECT
[LastDayOfThisMonth] = EOMONTH(GETDATE()),
[SecondLastDayOfThisMonth] = DATEADD(DAY,-1,EOMONTH(GETDATE()))
I think EOMONTH was introduced in SQL 2012 and this is the SQL 2008 board.

Good catch Jonathan, thanks.

https://blogs.msdn.microsoft.com/samlester/2013/09/23/eomonth-equivalent-in-sql-server-2008-r2-and-below/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

The standard pattern for getting the first day of the month is:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Stick to that pattern, don't corrupt it with -1 tricks! That just makes it more confusing later.

If you need the last day of the current month, then (1) add a month and then (2) subtract a day. Do this directly, not by using -1 for the day ,but by just subtracting a day. Keep the code consistent with the standard pattern and it will be much easier to understand, and thus to write and maintain.

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

below86 SSChampion Points: 11532 More actions · Answer 6

You should be able to pass any date to the code in the last statement to get the second to last date of the month based on the date used. Included the others for reference.

DECLARE @CurrentDate AS DATE;

SET @CurrentDate = GETDATE();

SELECT @CurrentDate AS CurrentDate, 
DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate) AS BeginMonthDate, 
DATEADD(MM, 1, DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate)) AS BeginNextMonthDate, 
DATEADD(DD, -1, DATEADD(MM, 1, DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate))) AS LastDayofMonthDate, 
DATEADD(DD, -2, DATEADD(MM, 1, DATEADD(DD, 1-(DAY(@CurrentDate)), @CurrentDate))) AS SecondtoLastDayofMonthDate 
;

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

drew.allen SSC Guru Points: 76985 More actions · Answer 7

ScottPletcher wrote:

The standard pattern for getting the first day of the month is:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Stick to that pattern, don't corrupt it with -1 tricks! That just makes it more confusing later.
If you need the last day of the current month, then (1) add a month and then (2) subtract a day. Do this directly, not by using -1 for the day ,but by just subtracting a day. Keep the code consistent with the standard pattern and it will be much easier to understand, and thus to write and maintain.
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))

I disagree. Using -1 is no more of a "trick" than using 0. I think that nesting multiple DATEADD functions is more confusing than using a single DATEADD function with a -1.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

below86 SSChampion Points: 11532 More actions · Answer 8

drew.allen wrote:

ScottPletcher wrote:
The standard pattern for getting the first day of the month is:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Stick to that pattern, don't corrupt it with -1 tricks! That just makes it more confusing later.
If you need the last day of the current month, then (1) add a month and then (2) subtract a day. Do this directly, not by using -1 for the day ,but by just subtracting a day. Keep the code consistent with the standard pattern and it will be much easier to understand, and thus to write and maintain.
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
I disagree. Using -1 is no more of a "trick" than using 0. I think that nesting multiple DATEADD functions is more confusing than using a single DATEADD function with a -1.
Drew

Maybe you have smarter people working with you Drew. But here and where I've been using the zero or negative 1 is more confusing to the novice SQL users. IMHO, using the DATEADD is a simple way to do this that can easily be followed, even by novice SQL users. In other words the KISS method.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 9

drew.allen wrote:

ScottPletcher wrote:
The standard pattern for getting the first day of the month is:
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Stick to that pattern, don't corrupt it with -1 tricks! That just makes it more confusing later.
If you need the last day of the current month, then (1) add a month and then (2) subtract a day. Do this directly, not by using -1 for the day ,but by just subtracting a day. Keep the code consistent with the standard pattern and it will be much easier to understand, and thus to write and maintain.
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
I disagree. Using -1 is no more of a "trick" than using 0. I think that nesting multiple DATEADD functions is more confusing than using a single DATEADD function with a -1.
Drew

The nested dateadd functions are explicit and even if they have a few more parenthesis are more predictable. Teaching people to add directly to dates can lead to some things that you might like a lot less. Getdate() +1 makes some sense, what about getdate() + .3456?

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

I guess I don't understand why anyone would write software based on the knowledge of novices. If it's important for a novice to be able to maintain something a bit esoteric or complex, write a comment. Better yet, teach the novices so they're not novices anymore but still write the comment!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 11

People often learn via patterns.

If I see a pattern like "DATEADD(<time_period>, DATEDIFF(..." in consistent code, I know already what the result of the main computation will be. I don't have to waste time looking for -1 or other one-time tricks embedded in the code. The code is thus easier to follow overall. And at 3AM in a dicey situation, it's a even bigger advantage.

I don't think destroying the pattern and forcing everyone to fully re-read every expression, every time, is worth it, just to save a very simple step. Since that's the way I see it, I will continue to stick to standard patterns and recommend others here do so too.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76985 More actions · Answer 12

I think it's a circular argument. You don't use it, because it's not familiar, but it's not familiar, because you don't use it. I've been using the -1 variant for years, and I never have an issue with confusing the two.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101095 More actions · Answer 13

There's at least one other thread besides this one where someone used it and got the wrong date, because they added a month after the calc with -1 in it. For accurate date calcs, you need to add/subtract all months before you deal with day(s).

Besides, people copy code and re-use it all the time. I want them copying the best-practice code not code with a quasi-kludge in it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 14

drew.allen wrote:

I think it's a circular argument. You don't use it, because it's not familiar, but it's not familiar, because you don't use it. I've been using the -1 variant for years, and I never have an issue with confusing the two.
Drew

Maybe you don't, and maybe if you stick to just using that style for day addition it works. But for someone new you're teaching them that they can do other kinds of date manipulation as well with getdate() + .... and that can get much uglier very quickly.