Find Last Day of the Month

Question

Post reply

Find Last Day of the Month

marianamaas

Grasshopper

Points: 23
More actions
May 9, 2011 at 11:15 pm

#250028

Comments posted to this topic are about the item Find Last Day of the Month

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

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

f.marquis Grasshopper Points: 22 More actions · Answer 1

Another way to do this :

1) Add 1 month

2) Substract the day number of the previous result :

DECLARE @mydate datetime='20080224'

SELECT DATEADD(day, -DATEPART(day, @Mydate), DATEADD(MONTH, 1, @mydate))

tommyh SSCertifiable Points: 6302 More actions · Answer 2

f.marquis (5/10/2011)
Another way to do this :
1) Add 1 month
2) Substract the day number of the previous result :
DECLARE @mydate datetime='20080224'
SELECT DATEADD(day, -DATEPART(day, @Mydate), DATEADD(MONTH, 1, @mydate))

One problem with this is... it doesnt work. Run the code for '20080331' and it returns '20080330'.

/T

f.marquis Grasshopper Points: 22 More actions · Answer 3

You're right :

I think I wrote the good instructions but a wrong SQL.

This might be better :

1) Add 1 month

2) Substract the day number of the previousresult :

DECLARE @mydate datetime='20080331'

SELECT DATEADD( day, -DATEPART(day, DATEADD(MONTH, 1, @mydate)), DATEADD(MONTH, 1, @mydate))

tommyh SSCertifiable Points: 6302 More actions · Answer 4

A quick search on this site and one could for instance find

http://www.sqlservercentral.com/Forums/Topic777289-1291-1.aspx

Scroll down to Lynn Pettis first post. I would recommend reading the whole post since there is also some interesting posts on the following pages.

/T

srn1972 Grasshopper Points: 11 More actions · Answer 5

Below is a one line statement to compute last date of the month for a given input date.

DECLARE @MyDate datetime

SET @MyDate = '2012/11/25'

SELECT DATEADD(DAY,DATEPART(DAY,DATEADD(MONTH,1,@MyDate))*-1,DATEADD(MONTH,1,@MyDate))

--Output result: 2012-11-30 00:00:00.000

sorter Grasshopper Points: 12 More actions · Answer 6

select dateadd(d,-1,left(CONVERT(varchar,CURRENT_TIMESTAMP,112),6)+'01')

marianamaas Grasshopper Points: 23 More actions · Answer 7

marianamaas

Grasshopper

Points: 23

May 10, 2011 at 4:50 am

#1322200

good one!

Per Siden Valued Member Points: 64 More actions · Answer 8

This one will do the trick with just three instructions (and no string conversions which are best avoided):

SELECT

"LastDayOfMonth" = DatePart(day, DateAdd(month, DateDiff(month, -1, GetDate()), -1))

LightVader Hall of Fame Points: 3810 More actions · Answer 9

I prefer this statement

DECLARE @myDate DATETIME

SELECT @myDate = GETDATE()

SELECT DATEADD(dd, -(DAY(DATEADD(mm, 1, @myDate))), DATEADD(mm, 1, @myDate))

And then to get the date without the time just plug it in here:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @myDate))

The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 10

Use this to get the last day of the month

DECLARE @date DATETIME

SELECT @date='2011/05/10'

SELECT DATEADD(DD, -1, DATEADD(MM, DATEDIFF(MM,0,@date)+1,0))

aaron-403220 SSC Enthusiast Points: 195 More actions · Answer 11

wallah!???

I think you mean voilà

A wallah is a servant...

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 12

SELECT DATEADD(MM,DATEDIFF(MM,-1,getdate()),0)-1

Regards,
Mitesh OSwal
+918698619998

Oliver Wootton SSC Veteran Points: 291 More actions · Answer 13

--Last day of the current month.

--You could format it and it also takes into account the timestamp - 3 milliseconds

SELECT DATEADD(MS,-3,DATEADD(MM, DATEDIFF(M,0,GETDATE() )+1, 0))

MiguelArvelo01 SSC Veteran Points: 237 More actions · Answer 14

DateTime already has this implemented...

DateTime.DaysInMonth(Year as Int32, Month as Int32)