代码之家 › 专栏 › 技术社区 › Prashant Pimpale Dila Gurung

如何按当前日期获取财务季度日期

datetime sql-server sql

Prashant Pimpale Dila Gurung · 技术社区 · 6 年前

季度描述如下:

APRIL - JUNE   - Q1
JULY  - SEPT   - Q2
OCT   - DEC    - Q3
JAN   - MARCH  - Q4

对于EX:

Date = '2018-12-24'  -- Where this date is under third quarter

所以预期应该是[获取所有季度<当前日期的季度]

---------------------------
Quarters
---------------------------
01 Jul 2018 - 30 Sep 2018
01 Apr 2018 - 30 Jun 2018
---------------------------

如果

Date = '2019-01-24' -- Where this date is under fourth quarter

预期结果:

---------------------------
Quarters
---------------------------
01 Oct 2018 - 30 Dec 2018
01 Jul 2018 - 30 Sep 2018
01 Apr 2018 - 30 Jun 2018
---------------------------

我尝试的查询:

DECLARE @dt DATETIME = '2019-01-24 18:15:59.517' 
DECLARE @FirstDayOfQuarter DATETIME 
DECLARE @LastDayOfQuarter DATETIME 

SET @FirstDayOfQuarter = (SELECT Dateadd(qq, Datediff(qq, 0, @dt) - 1, 0)) 
SET @LastDayOfQuarter = (SELECT Dateadd(dd, -1, Dateadd(qq, Datediff(qq, 0, @dt) 
                                                , 0))) 

DECLARE @year INT 

SET @year = Datepart(year, @dt) 

DECLARE @currQ NVARCHAR(max) 

SET @currQ = (SELECT CONVERT(VARCHAR(20), @FirstDayOfQuarter, 106) 
                     + Space(1) + '-' + Space(1) 
                     + CONVERT(VARCHAR(20), @LastDayOfQuarter, 106)) 

SELECT CONVERT(NVARCHAR(20), Dateadd(m, 3*number, CONVERT(DATE, CONVERT(VARCHAR( 
       5), 
       @year)+'-1-1')), 106) 
       + Space(1) + '-' + Space(1) 
       + CONVERT(NVARCHAR(20), Dateadd(d, -1, Dateadd(m, 3*number+3, CONVERT( 
       DATE, 
       CONVERT(VARCHAR(5), @year)+'-1-1'))), 106) AS Quarter, 
       number, 
       CASE 
         WHEN @dt BETWEEN Dateadd(m, 3 * number, CONVERT(DATE, CONVERT(VARCHAR(5 
                                                               ), 
                                                               @year) + 
                                                               '-1-1')) AND 
                                 Dateadd(d, -1, Dateadd(m, 3 * number + 3, 
                                                CONVERT(DATE, CONVERT(VARCHAR(5) 
                                                              , 
                          @year 
                          ) + '-1-1'))) THEN 1 
         ELSE 0 
       END                                        AS isCurrentQuarter 
INTO   #allquarters 
FROM   master..spt_values 
WHERE  type = 'p' 
       AND number BETWEEN 1 AND 4 

SELECT TOP 1 number, 
             CASE 
               WHEN @dt BETWEEN Dateadd(m, 3 * number, 
                                CONVERT(DATE, CONVERT(VARCHAR(5), 
                                              @year) + 
                                              '-1-1')) AND 
                                       Dateadd(d, -1, Dateadd(m, 3 * number + 3, 
                                                      CONVERT(DATE, CONVERT( 
                                                      VARCHAR(5), 
                                @year 
                                ) + '-1-1'))) THEN 1 
               ELSE 0 
             END AS isCurrentQuarter 
INTO   #currentquarter 
FROM   master..spt_values 
WHERE  type = 'p' 
       AND number BETWEEN 1 AND 4 
ORDER  BY iscurrentquarter DESC, 
          number ASC 

SELECT quarter 
FROM   #allquarters 
WHERE  number < (SELECT number 
                 FROM   #currentquarter) 
ORDER  BY number DESC 

DROP TABLE #allquarters 

DROP TABLE #currentquarter

它是为 GETDATE() 但是当我把日期 2019-01-24 18:15:59.517 然后它给了我空的结果

编辑:

如果

Date = '2019-05-24' -- Where this date is under first quarter of next year

预期结果:

---------------------------
Quarters
---------------------------
01 Jan 2019 - 30 March 2019
01 Oct 2018 - 30 Dec 2018
01 Jul 2018 - 30 Sep 2018
01 Apr 2018 - 30 Jun 2018
---------------------------

如果

Date = '2018-05-24' -- Where this date is under the first quarter of current year

预期结果:

---------------------------
Quarters
---------------------------
01 Jan 2018 - 30 March 2018
01 Oct 2017 - 30 Dec 2017
01 Jul 2017 - 30 Sep 2017
01 Apr 2017 - 30 Jun 2017
---------------------------

2 回复 | 直到 6 年前

Salman Arshad 6 年前

使用公式 (n + 5) % 12 + 3 要转换月份号1,2,…,12到9,10,…,14,3,…,8,这是您需要从给定日期中减去的月份数。在SQL Server 2008中,您可以将其翻译为:

DATEADD(MONTH, DATEDIFF(MONTH, 0, inputdate) - ((MONTH(inputdate) + 5) % 12 + 3), 0)

皈依者 2018-04-xx 到 2017-04-01 和 2018-07-xx 到 2018-04-01 . 增加3个月、6个月和9个月是微不足道的。

DB Fiddle

Gordon Linoff 6 年前

我相信这是你想要的:

with dte as (
      select cast('2019-07-24' as date) as dte
     )
select dte,
       (convert(varchar(255), dateadd(month, v.n, datefromparts(year(dte), ((month(dte) - 1) / 3) * 3 + 1, 1)), 106) + ' - ' +
        convert(varchar(255), dateadd(day, -1, dateadd(month, v.n + 3, datefromparts(year(dte), ((month(dte) - 1) / 3) * 3 + 1, 1))), 106)
      ) as string 
from dte cross apply
     (values (0), (-3), (-6), (-9)) v(n)
where month(dte) < 4 or month(dte) >= 13 + v.n
order by v.n;

Here 是db<>小提琴。