last day of month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

last day of month

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-11 : 04:51:22

hi,
I am using the following sql to work out the last day of the previous month, but it is show the 29th Jan instead of 31st Jan.
Do you know how I can resolve this ?

SELECT dateadd(mm,-1,dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0)))

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-11 : 04:57:06

Use this statement instead

SELECT dateadd(mm, DATEDIFF(m,0,getdate()), 0)-1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 05:03:13

[code]SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231'),
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)[/code]

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

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-11 : 05:06:36

thank you, and that sql is much easier to understand !

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-11 : 05:10:52

quote:
Originally posted by jamie

thank you, and that sql is much easier to understand !

Which sql?

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-11 : 05:18:03

Either of them,
I am using this one
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)
However, I have another problem, what happens if I want to say last day 2 months ago, or last day 3 months ago ?
I thought I could change the -1 , but that diesn't work..

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-11 : 05:22:22

Use like this to get the last day 2 months ago

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 05:23:28

-1 is the number of days, not months.

DECLARE	@MonthsBack SMALLINT

SET	@MonthsBack = 0

SELECT	DATEADD(MONTH, @MonthsBack + DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

SET	@MonthsBack = -1

SELECT	DATEADD(MONTH, @MonthsBack + DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

SET	@MonthsBack = -2

SELECT	DATEADD(MONTH, @MonthsBack + DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

SET	@MonthsBack = 12

SELECT	DATEADD(MONTH, @MonthsBack + DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

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

jamie
Aged Yak Warrior

542 Posts

Posted - 2008-02-11 : 05:29:05

ah, I see. thank you guys.
I like ayamas solution !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources