根据输入的SQL Server和SSRS年份计算财政年度的开始和结束日期

使用 DATEADD 和 DATEDIFF 您可以在您的财政年度使用计算机:

DECLARE @year INT = 2015

SELECT
    start_date = DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)) - 1, 0)),
    end_date = DATEADD(DAY, -1, DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)), 0)))

阅读 here 了解更多常见的日期例程。

要在存储过程中使用它,请执行以下操作:

CREATE PROCEDURE procedure_name
    @year AS INT
AS
BEGIN
SET NOCOUNT ON

SELECT
    start_date = DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)) - 1, 0)),
    end_date = DATEADD(DAY, -1, DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)), 0)))

END

对于SQL server 2012+版本,可以使用 DATEFROMPARTS https://msdn.microsoft.com/en-IN/library/hh213228.aspx

CREATE PROCEDURE [dbo].[usp_name] 
     @StartDate as datetime,
     @Enddate as datetime,
     @year as int
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON; 

    SELECT @StartDate = DATEFROMPARTS(@year-1,7,1), @EndDate=DATEFROMPARTS(@year,6,30)
END

也许这会有所帮助,而且在财政年度发生变化或你跳槽到新公司时也会奏效。

CREATE PROCEDURE [dbo].[usp_yeardates] /* or your sp name */
    @year AS SMALLINT,
    @monthoffset AS TINYINT = 0, /* if you wish your year to start at a month other than jan then set number of months to offset, e.g. to start April, move three forward @monthoffset = 3 */
    @startdate AS SMALLDATETIME OUTPUT, /* NB 2008r2+ use DATE instead of SMALLDATETIME */
    @enddate AS SMALLDATETIME OUTPUT
AS

/* Created by Darren Edward Comeau - 26/08/2015 */

BEGIN

    /* check inputs */
    IF @year < 1900 or @year > 2078
        RAISERROR ('year out of bounds',16,1)

    ELSE IF @monthoffset > 11
        RAISERROR ('monthoffset out of bounds',16,2)

    ELSE
        SELECT
            /* logic to establish start / end date */
            @startdate =
                dateadd(month,@monthoffset,
                    dateadd(year,@year-1900,'19000101')
                ),
            @enddate =
                dateadd(day,-1,
                    dateadd(month,@monthoffset,
                        dateadd(year,@year-1899,'19000101')
                    )
                );

END;
GO

您可以使用以下程序:;

/* usage */
DECLARE
    @startdate SMALLDATETIME,
    @enddate SMALLDATETIME,
    @year SMALLINT,
    @monthoffset TINYINT,
    @rc INT;

EXEC @rc = usp_yeardates
    @year = 2011,
    @monthoffset = 6, /* 6 months offset equalls July - June year */
    @startdate = @startdate OUTPUT,
    @enddate = @enddate OUTPUT;

SELECT
    @rc AS [ReturnCode],
    @startdate AS [StartDate],
    @enddate AS [EndDate];

这将为您提供财政年度的开始和结束日期

DECLARE  @DateFrom datetime, @DateTo   datetime
SET @DateFrom = '2012-03-30'
SET @DateTo = '2021-03-31'

DECLARE @Output TABLE ( Item NVARCHAR(1000),startdate Datetime,enddate Datetime )
DECLARE @Year INT
DECLARE @EndYear INT
DECLARE @Month INT
DECLARE @FinacialYearValue INT

SET @Month = CAST(MONTH(@DateFrom) AS INT)
SET @Year = CAST(YEAR(@DateFrom) AS INT)
SET @EndYear= CAST(YEAR(@DateTo) AS INT)
SET @FinacialYearValue = (CASE WHEN @Month <=3 THEN @Year - 1 ELSE @Year END)

WHILE @EndYear >= @FinacialYearValue
BEGIN
    INSERT INTO @Output (Item,startdate,enddate )
       SELECT CAST(@FinacialYearValue AS varchar(4))  + '-' + CAST((@FinacialYearValue +1 )  AS varchar(4)) ,
       start_date = DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @FinacialYearValue+1 - 1900, 0)) - 1, 0)),
       end_date = DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @FinacialYearValue+1 - 1900, 0)), 0)))

    SET @FinacialYearValue += 1
END

SELECT * FROM @Output

Declare @BillDate smalldatetime, @FYStartDate smalldatetime, @FYEndDate smalldatetime
Select @BillDate ='10-JAN-2019'

Select @FYStartDate = case when MONTH(@BillDate) <=3 Then '01-Apr-' + CAST(YEAR(@BillDate) - 1 AS varchar(4)) Else '01-Apr-' + CAST(YEAR(@BillDate)  AS varchar(4)) End,
@FYEndDate = case when MONTH(@BillDate) <=3 Then '31-Mar-' + CAST(YEAR(@BillDate)  AS varchar(4)) Else '31-Mar-' + CAST(YEAR(@BillDate) + 1  AS varchar(4)) End

以下代码适用于Azure SQL DB和内部部署。根据您的需要/愿望更改时区。( 已测试单元 )

如果我们给出了当前日期或我们希望给出的任何日期,下面的代码将帮助我们将财务年度的开始日期和结束日期作为结果集。

还想提一提的是,财政年度的逻辑仅就年度而言并不奏效。请检查以下两种情况。 2019年-->可能是3月31日,也可能是4月1日。如果我们不指定日期,最后会出错。 日期- 20190331->财务开始日期为20180401,财务结束日期为20190331。 20190401->财务开始日期为20190401,财务结束日期为20200331。

DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time'; -- GETDATE()
--  SET @Current_DateTime='20200312'; -- uncomment this line to test with your desired date.

SELECT DATEFROMPARTS(Yr, 4, 1) AS FinancialYear_StartDate,
DATEFROMPARTS(Yr + 1, 3, 31) AS FinancialYear_EndDate,
CONCAT(Yr,'-',Yr+1) AS FinancialYear
FROM
(SELECT CASE WHEN DATEPART(MONTH, @Current_DateTime ) < 4 
THEN DATEPART(YEAR, @Current_DateTime ) - 1 ELSE DATEPART(YEAR, @Current_DateTime ) END Yr) a;

CREATE PROC udp_financial_year
AS
BEGIN
SELECT DISTINCT
CASE 
WHEN DATEPART(MONTH,creationdate) <10 then 
CONVERT(VARCHAR,DATEPART(YEAR,creationdate)-1)+'-'+
CONVERT(VARCHAR,DATEPART(YEAR,creationdate)) 
ELSE 
CONVERT(VARCHAR,DATEPART(YEAR,creationdate))+'-'+ 
CONVERT(VARCHAR,DATEPART(YEAR,creationdate)+1)
END 
financialyr
FROM testing
ORDER BY financialyrDESC
END

这将提供印度财政年度的开始日期。i、 g.四月至三月。

SELECT CAST(DATEFROMPARTS(YEAR(DATEADD(M, (MONTH(DATEADD(MONTH, -4, GETDATE()) - 1) * -1), GETDATE())), MONTH('04-01-2020'), DAY('04-01-2020')) AS date)

Declare @Date date = Getdate() ---Paste your date here

Declare @Fyear  varchar(4)

Declare @FyearStartDate Date

Declare @FyearEnd varchar(4)

Declare @FyearEndDate Date

If Month(@Date) >= 4 
Set @Fyear             = year(@Date)
Else
Set  @Fyear             = year(@Date)-1

Set @FyearStartDate  =  '04' +'-'+ '01-' + @Fyear

Set @FyearEnd             = @Fyear+1

Set @FyearEndDate    =  '03' +'-'+ '31-' + @FyearEnd

Select @FyearStartDate FYSTARTDT, @FyearEndDate FYENDDT