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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1433 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
15732 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
52326 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

1433 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
52326 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
52326 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  
 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.08 seconds. Powered By: Snitz Forums 2000