产生财务计帐周期，

产生财务计帐周期，

 

先看看实现的结果，可以By月份和季度，可以调整会计开始日期。

 

前端略去，只分享MS SQL存储过程：

 

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Blog: https://insus.cnblogs.com -- Create date: 2019-07-02 -- Update date: 2019-07-02 -- Description: 动态产生会计周期 -- ============================================= CREATE PROCEDURE [dbo].[usp_AccountingPeriod_Select_GeneratePeriodDate] ( @Fiscal_Year SMALLINT, @PeriodDateType NVARCHAR(1), @Start1 DATETIME ) AS BEGIN IF OBJECT_ID('#Period_Date') IS NOT NULL DROP TABLE #Period_Date CREATE TABLE #Period_Date( [Fiscal_Year] [smallint] NOT NULL, [Start1] [datetime] NULL, [Start2] [datetime] NULL, [Start3] [datetime] NULL, [Start4] [datetime] NULL, [Start5] [datetime] NULL, [Start6] [datetime] NULL, [Start7] [datetime] NULL, [Start8] [datetime] NULL, [Start9] [datetime] NULL, [Start10] [datetime] NULL, [Start11] [datetime] NULL, [Start12] [datetime] NULL, [Start13] [datetime] NULL, [End1] [datetime] NULL, [End2] [datetime] NULL, [End3] [datetime] NULL, [End4] [datetime] NULL, [End5] [datetime] NULL, [End6] [datetime] NULL, [End7] [datetime] NULL, [End8] [datetime] NULL, [End9] [datetime] NULL, [End10] [datetime] NULL, [End11] [datetime] NULL, [End12] [datetime] NULL, [End13] [datetime] NULL ) IF @Fiscal_Year >= YEAR([dbo].[svf_LowDate]()) AND @Fiscal_Year < YEAR([dbo].[svf_HighDate]()) BEGIN IF @Start1 IS NULL SET @Start1 = CONVERT(DATETIME, CONVERT(NVARCHAR(4),@Fiscal_Year) + '-01-01', 121) INSERT INTO #Period_Date ([Fiscal_Year]) VALUES (@Fiscal_Year) IF @PeriodDateType = N'M' BEGIN DECLARE @m TINYINT = 1,@ms TINYINT = 12 WHILE @m <= @ms BEGIN DECLARE @m_start_field NVARCHAR(128) = N'[Start'+ CONVERT(NVARCHAR(2), @m) +']' DECLARE @m_start_value DATETIME = DATEADD(M,@m -1,@Start1) DECLARE @m_end_field NVARCHAR(128) = N'[End'+ CONVERT(NVARCHAR(2), @m) +']' DECLARE @m_end_value DATETIME = DATEADD(DAY,-1, DATEADD(M,@m,@Start1)) DECLARE @s_sql NVARCHAR(4000) = N' UPDATE #Period_Date SET '+ @m_start_field +' = '''+ CONVERT(NVARCHAR(40), @m_start_value ) +''', '+ @m_end_field +' = '''+ CONVERT(NVARCHAR(40), @m_end_value ) +''' WHERE [Fiscal_Year] = '''+ CONVERT(NVARCHAR(4),@Fiscal_Year) +'''' EXECUTE sp_executesql @s_sql SET @m = @m + 1 END END IF @PeriodDateType = N'Q' BEGIN DECLARE @q TINYINT = 1,@qs TINYINT = 4 WHILE @q <= @qs BEGIN DECLARE @q_start_field NVARCHAR(128) = N'[Start'+ CONVERT(NVARCHAR(2), @q) +']' DECLARE @q_start_value DATETIME = DATEADD(QUARTER,@q -1,@Start1) DECLARE @q_end_field NVARCHAR(128) = N'[End'+ CONVERT(NVARCHAR(2), @q) +']' DECLARE @q_end_value DATETIME = DATEADD(DAY,-1, DATEADD(QUARTER,@q,@Start1)) DECLARE @q_s_sql NVARCHAR(4000) = N' UPDATE #Period_Date SET '+ @q_start_field +' = '''+ CONVERT(NVARCHAR(40), @q_start_value ) +''', '+ @q_end_field +' = '''+ CONVERT(NVARCHAR(40), @q_end_value ) +''' WHERE [Fiscal_Year] = '''+ CONVERT(NVARCHAR(4),@Fiscal_Year) +'''' EXECUTE sp_executesql @q_s_sql SET @q = @q + 1 END END END SELECT [Fiscal_Year], [Start1],[Start2],[Start3],[Start4], [Start5],[Start6],[Start7],[Start8], [Start9],[Start10],[Start11],[Start12], [Start13], [End1],[End2],[End3],[End4], [End5],[End6],[End7],[End8], [End9],[End10],[End11],[End12], [End13] FROM #Period_Date END Source Code