months in quarter - 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 2008 Forums

Transact-SQL (2008)

months in quarter

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-01 : 10:13:43

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

15732 Posts

Posted - 2012-11-01 : 10:18:44

[code]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)))[/code]FYI this works for any date value, not just the last day of a month or quarter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-01 : 10:19:03

[code]
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
[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-01 : 11:12:38

Thank you

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-01 : 16:33:24

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-01 : 16:38:37

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 00:20:19

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/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-11-04 : 03:00:27

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 11:43:43

[code]
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

[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources