普通日期转换为Fiscal Year， Fiscal Month， Fiscal Week

时间：2022-03-09 09:09:02

现在许多公司做报表 通常使用 Fiscal Date，但是通常我们收集到的数据是Calendar Date，所以他们之间必然需要一种转换。以下仅适用于财年不变的情况，欢迎指正和改进

Fiscal Year，Fiscal Month的转换比较简单，比如公司一个财年的startDate 为 July 01， endDate 为June 30.那么只要判断月份是否大于6即可确定Fiacal Year和Fiscal Month

 
DECLARE @testDate DATETIME;
SET @testDate=GETDATE();
SELECT      
CASE WHEN MONTH(@testDate) > 6 THEN YEAR(@testDate) + 1 ELSE YEAR(@testDate) END AS FiscalYear,
CASE WHEN MONTH(@testDate) > 6 THEN MONTH(@testDate) - 6 ELSE MONTH(@testDate) + 6 END AS FiscalMonth

但是Fiscal Week就需要动一番脑筋了，因为每个月份的week并不确定，并且有可能是跨年的，所以要注意的因素多一点，首先我们把问题分解：

1. 月份大于6

示例：2011/11/25 这个日期的Fiscal Year是2012，Fiscal Month 是May。

Fiscal Week的计算：当前的week数减去财年开始的week数，由于财年开始的那个week，Fiscal Week是1，所以需要再加上1

DATEPART(WEEK,@testDate)-DATEPART(WEEK,CAST(('07/01/'+CAST(YEAR(@testDate) AS NVARCHAR(4))) AS SMALLDATETIME))+1

2. 月份小于六月

示例：2012/01/02这个日期的Fiscal Year是2010， Fiscal Year是July。
 Fiscal Week的计算：直觉上想是last year的后六个月的Fiscal Week+ 今年的week数就行了，但是有一点需要考虑就是同一个week有肯能跨年 比如2010/12/31和2011/01/01是在同一个week的，所以我们不能重复加，所以我们要做的就是判断12/31这一天是不是一个week的最后一天然后确定是否要减1，计算如下

DATEPART(WEEK,CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))
-DATEPART(WEEK,CAST(('07/01/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))+1+ 
DATEPART(WEEK,@testDate)-CASE WHEN  DATEPART(DW, CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))=7 THEN 0 ELSE 1 END   AS FiscalWeek

综合起来计算Fiscal Week的方法：

SELECT
　　CASE 
WHEN MONTH(@testDate) > 6
THEN DATEPART(WEEK,@testDate)-
DATEPART(WEEK,CAST(('07/01/'+CAST(YEAR(@testDate) AS NVARCHAR(4))) AS SMALLDATETIME))+1
ELSE DATEPART(WEEK,CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))-
DATEPART(WEEK,CAST(('07/01/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))+
DATEPART(WEEK,@testDate)+ 
CASE WHEN
DATEPART(DW, CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))=7
THEN 1
ELSE 0
END
   　　 END AS FiscalWeek

测试

declare @testDate dateTime

set @testDate = '01/02/2011'
SELECT 
CASE WHEN MONTH(@testDate) > 6 THEN YEAR(@testDate) + 1 ELSE YEAR(@testDate) END AS FiscalYear,
CASE WHEN MONTH(@testDate) > 6 THEN MONTH(@testDate) - 6 ELSE MONTH(@testDate) + 6 END AS FiscalMonth,
CASE WHEN MONTH(@testDate) > 6 THEN LEFT((DATENAME(MONTH,DATEADD(MONTH,-6,@testDate))),3) ELSE LEFT((DATENAME(MONTH,DATEADD(MONTH,6,@testDate))),3) END AS FiscalMonthName,
CASE 
WHEN MONTH(@testDate) > 6
THEN DATEPART(WEEK,@testDate)-
DATEPART(WEEK,CAST(('07/01/'+CAST(YEAR(@testDate) AS NVARCHAR(4))) AS SMALLDATETIME))+1
ELSE DATEPART(WEEK,CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))-
DATEPART(WEEK,CAST(('07/01/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))+
DATEPART(WEEK,@testDate)+ 
CASE WHEN
DATEPART(DW, CAST(('12/31/'+CAST((YEAR(@testDate)-1) AS NVARCHAR(4))) AS SMALLDATETIME))=7
THEN 1
ELSE 0
END
END AS FiscalWeek 

--结果
--FiscalYear    FiscalMonth    FiscalMonthName    FiscalWeek
--2011    7    Jul    28






 

 

 

标签：

相关文章