计算给定四分之一SQL的开始日期和结束日期

Question

我想获得：

从日期之间的给定季度开始的开始日期和终点

示例：

日期范围：2016-01-01-2016-12-31

1（季度） - 会给我：

start date
2016-01-01
enddate
2016-03-31

2（季度） - 会给我：

start date
2016-04-01
enddate
2016-06-30

等等

Answer 1

我仅获得了季度和年份，并根据您的需要进行了修改

-- You may need to extend the range of the virtual tally table.
SELECT [QuarterName] = 'Q' + DATENAME(qq,DATEADD(QQ,n,startdate)) + ' ' + CAST(YEAR(DATEADD(QQ,n,startdate)) AS VARCHAR(4))
FROM (SELECT startdate = '01/Jan/2016', enddate = '31/DEC/2016') d
CROSS APPLY (
   SELECT TOP(1+DATEDIFF(QQ,startdate,enddate)) n 
   FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) rc(n)
) x 

Answer 2

检查下面的逻辑以获取答案。

DECLARE @Year DATE = convert(varchar(20),datepart(YEAR,getdate()))+'-01'+'-01'
    DECLARE @Quarter INT = 4
    SELECT  DATEADD(QUARTER, @Quarter - 1, @Year) ,
            DATEADD(DAY, -1, DATEADD(QUARTER,  @Quarter, @Year))

Answer 3

SELECT DATEADD(QUARTER, d.q, DATEADD(YEAR, DATEDIFF(YEAR, 0,GETDATE()), 0))
AS FromDate,
DATEADD(QUARTER, d.q + 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))
AS ToDate
FROM (
        SELECT 0 UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3
     ) AS d(q)