| Author |
 Topic  |
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
 Posted - 11/01/2012 : 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
USA
15559 Posts |
Posted - 11/01/2012 : 10:18:44
|
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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 11/01/2012 : 10:19:03
|
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/
|
|
|
|
arkiboys
Flowing Fount of Yak Knowledge
1341 Posts |
Posted - 11/01/2012 : 11:12:38
|
| Thank you |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/01/2012 : 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 |
Edited by - Michael Valentine Jones on 11/01/2012 16:34:40 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 11/01/2012 : 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 |
Edited by - Michael Valentine Jones on 11/01/2012 16:40:18 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 11/04/2012 : 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)
USA
6997 Posts |
Posted - 11/04/2012 : 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
India
47173 Posts |
Posted - 11/04/2012 : 11:43:43
|
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/
|
|
|
| |
Topic |
|
|
|
months in quarter
