Find Last Day of Month

Question

Post reply

Find Last Day of Month

Viewing 4 posts - 16 through 18 (of 18 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SELECTDATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS LastOfPreviousMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101') AS FirstOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '18991231', GETDATE()), '18991231') AS LastOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, '18991231', GETDATE()), '19000101') AS FirstOfNextMonth

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

And with integers as parameter values

SELECTDATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastOfPreviousMonth,

DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1) AS LastOfCurrentMonth,

DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), 0) AS FirstOfNextMonth

N 56°04'39.16"
E 12°55'05.25"

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Jeff,

Really Great!

(No character based date conversions) + (No concatenation) = Very high speed short code.

You taught me a new concept.

Thanks a lot for your prompt reply with good explanations !

But i am getting the error message while executing the code. Yes.

DATEDIFF(mm,0,@DateStart)

When i replace '0' with some other date it is giving the result.

If i leave as it is, it is showing like

DATEDIFF function is invoked with wrong parameter(s).

Please correct me where i am doing mistake.

In the meantime, please try to figure out what dateformat you'd like for startdate and enddate... I recommend not using one at all.

Apr/01/2007

Sorry As midnight time was here i am unable to see your reply.I saw it today morning only.

karthik

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

The code I posted work without error on my end. If you are typing, are you sure that your typing a zero and not an "OH"?

You know the rules... post the code you're having a problem with... 😉

--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)