SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 months in quarter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/01/2012 :  10:13:43  Show Profile  Reply with Quote
Hi,
How do I get the last date of each month in a quarter?
For example: I pass in 30 sep 2012 and I expect to get :
31/7/2012
31/8/2012
30/9/2012

Thanks

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/01/2012 :  10:18:44  Show Profile  Visit robvolk's Homepage  Reply with Quote
DECLARE @date DATETIME='2012-09-30'
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(q,DATEDIFF(q,0,@date),0)))
UNION ALL
SELECT DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(q,DATEDIFF(q,0,@date),0)))
UNION ALL
SELECT DATEADD(DAY,-1,DATEADD(MONTH,3,DATEADD(q,DATEDIFF(q,0,@date),0)))
FYI this works for any date value, not just the last day of a month or quarter.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/01/2012 :  10:19:03  Show Profile  Reply with Quote

DECLARE @Date datetime
SET @Date = '20120930'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start)
FROM Dates
WHERE DATEADD(mm,1,Start) < = @Date
)

SELECT *
FROM Dates


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 11/01/2012 :  11:12:38  Show Profile  Reply with Quote
Thank you
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/01/2012 :  16:33:24  Show Profile  Reply with Quote
The last day of the month for each month of the quarter can be calculated directly with a single dateadd/datediff.

select
	a.DT,
	QtrMonth1LastDay	= dateadd(mm,(datediff(qq,-1,a.DT)*3)-2,-1),
	QtrMonth2LastDay	= dateadd(mm,(datediff(qq,-1,a.DT)*3)-1,-1),
	QtrMonth3LastDay	= dateadd(qq,datediff(qq,-1,a.DT),-1)
from
	( -- test dates
	select DT = getdate()	union all
	select DT = '17530101'	union all
	select DT = '18990101'	union all
	select DT = '18991230'	union all
	select DT = '20120115'	union all
	select DT = '20120427'	union all
	select DT = '20120718'	union all
	select DT = '20120930'	union all
	select DT = '20121230'
	) a
order by
	a.DT


Results:
DT                      QtrMonth1LastDay        QtrMonth2LastDay        QtrMonth3LastDay
----------------------- ----------------------- ----------------------- -----------------------
1753-01-01 00:00:00.000 1753-01-31 00:00:00.000 1753-02-28 00:00:00.000 1753-03-31 00:00:00.000
1899-01-01 00:00:00.000 1899-01-31 00:00:00.000 1899-02-28 00:00:00.000 1899-03-31 00:00:00.000
1899-12-30 00:00:00.000 1899-10-31 00:00:00.000 1899-11-30 00:00:00.000 1899-12-31 00:00:00.000
2012-01-15 00:00:00.000 2012-01-31 00:00:00.000 2012-02-29 00:00:00.000 2012-03-31 00:00:00.000
2012-04-27 00:00:00.000 2012-04-30 00:00:00.000 2012-05-31 00:00:00.000 2012-06-30 00:00:00.000
2012-07-18 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
2012-09-30 00:00:00.000 2012-07-31 00:00:00.000 2012-08-31 00:00:00.000 2012-09-30 00:00:00.000
2012-11-01 16:33:13.663 2012-10-31 00:00:00.000 2012-11-30 00:00:00.000 2012-12-31 00:00:00.000
2012-12-30 00:00:00.000 2012-10-31 00:00:00.000 2012-11-30 00:00:00.000 2012-12-31 00:00:00.000

(9 row(s) affected)





CODO ERGO SUM

Edited by - Michael Valentine Jones on 11/01/2012 16:34:40
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/01/2012 :  16:38:37  Show Profile  Reply with Quote
quote:
Originally posted by visakh16


DECLARE @Date datetime
SET @Date = '20120930'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start)
FROM Dates
WHERE DATEADD(mm,1,Start) < = @Date
)

SELECT *
FROM Dates


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I don't think that one really works:
DECLARE @Date datetime
SET @Date = '20121231'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start)
FROM Dates
WHERE DATEADD(mm,1,Start) < = @Date
)

SELECT *
FROM Dates

Results:
Start
-----------------------
2012-10-31 00:00:00.000
2012-11-30 00:00:00.000
2012-12-30 00:00:00.000

(3 row(s) affected)

CODO ERGO SUM

Edited by - Michael Valentine Jones on 11/01/2012 16:40:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2012 :  00:20:19  Show Profile  Reply with Quote
yep...Nice catch

it should be


DECLARE @Date datetime
SET @Date = '20121231'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start+1)-1
FROM Dates
WHERE DATEADD(mm,1,Start+1)-1 < = @Date
)

SELECT *
FROM Dates

output
----------------------------------
Start
2012-10-31 00:00:00.000
2012-11-30 00:00:00.000
2012-12-31 00:00:00.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/04/2012 :  03:00:27  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

yep...Nice catch

it should be


DECLARE @Date datetime
SET @Date = '20121231'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start+1)-1
FROM Dates
WHERE DATEADD(mm,1,Start+1)-1 < = @Date
)

SELECT *
FROM Dates

output
----------------------------------
Start
2012-10-31 00:00:00.000
2012-11-30 00:00:00.000
2012-12-31 00:00:00.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





I think that only works if you pass the last day of the quarter.
DECLARE @Date datetime
SET @Date = '20121230'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start
UNION ALL
SELECT DATEADD(mm,1,Start+1)-1
FROM Dates
WHERE DATEADD(mm,1,Start+1)-1 < = @Date
)

SELECT *
FROM Dates

Results:
Start
-----------------------
2012-10-31 00:00:00.000
2012-11-30 00:00:00.000

(2 row(s) affected)


CODO ERGO SUM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/04/2012 :  11:43:43  Show Profile  Reply with Quote

DECLARE @Date datetime
SET @Date = '20130206'

;With Dates
AS
(
SELECT DATEADD(mm,1,DATEADD(qq,DATEDIFF(qq,0,@Date),0))-1 AS Start,DATEPART(qq,@date) AS Qr
UNION ALL
SELECT DATEADD(mm,1,Start+1)-1,DATEPART(qq,DATEADD(mm,1,Start+1)-1)
FROM Dates
WHERE DATEPART(qq,DATEADD(mm,1,Start+1)-1)  = DATEPART(qq,@date)
)

SELECT Start
FROM Dates

output
-----------------------------
Start
-----------------------------
2013-01-31 00:00:00.000
2013-02-28 00:00:00.000
2013-03-31 00:00:00.000



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000