Calculate financial year start and end date based on year entered SQL Server and SSRS

Question

I have report, which takes YEAR as one parameter and I wanted to calculate the start and end of the financial year. Here is how I'm trying:

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

    @StartDate = year(dateadd(q, -1, cast(cast(@year AS char) + '/01/' + cast(@year AS char) AS datetime))) = @year

Is this the correct way to do this?

I need financial start date as 1-July-2014 to 30-June-2015, if year entered as 2015.Please note that, this I need internally to be calculated in script. If I'm doing something wrong, how can I correct this to get desired results?

Clothes

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! – Traveller

Also: @year really always is a numerical value - so why are you declaring that parameter as varchar(10) then?? Use the most appropriate datatype - an INT makes a whole lot more sense here! – Traveller

Thank you. I'll correct all these during my real implementation. – Clothes

Answer 1

Using DATEADD and DATEDIFF you can computer for your fiscal years:

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)))

Read here for more common date routines.

---

To use this in a stored procedure:

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

Coulter

I'm using SQL 2008. This gives results as Jul 1 2014 12:00AM and Jun 30 2015 12:00AM. Report running with this parameter is returning less records. However, report running with this '1-JUL-2014' and '30-JUN-2015' is returning more numbers of records, this I believe correct. So, how can I make start and end date as '1-JUL-2014' and '30-JUN-2015' ? – Clothes

Answer 2

For SQL server 2012+ versions, you can use 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

Robbegrillet

I'm using Sql server 2008. Need a solution, that works in 2008 version. – Clothes

Answer 3

Perhaps this will help and also works when the financial year has changed or you move to a new company.

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

You would use the procedure as follows;

/* 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];

Derma

your problem with the date constraints would probably relate to the last day of the month, you exclude the entire day's records if they include the date portion. This can be tackled in two ways, use the next day from the last day and use a less than < operator or secondly, remove the time component from your date field where cast(convert(char(8),[mydatefield],112) as datetime) between startdate and enddate. This first will perform better in many circumstances – Derma

Answer 4

This will give you financial year's as well start and end date

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

Confetti

Answer 5

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

Nor

Answer 6

The below code would well in both Azure SQL DB and On-premises. Change your Time Zone as per your need/wish. (Unit Tested)

If we given the current date or whatever date we wish to give, the below code would help us to give us both the financial year start and end date as result sets.

Also want to mention that the logic for financial year is not works well on year alone.please check below 2 cases. Year- 2019 --> it may be either March 31 or April 1. if we dont specify date, we willl end up with errors. Date - 20190331 --> financial start date is 20180401 and financial end date - 20190331. 20190401 --> financial start date is 20190401 and financial end date - 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;

Introit

Would you like to add some explanation to your code-only answer? It would help fighting the misconception that StackOverflow is a free programming service. – Morbific

Thanks @Morbific for your valuable feedbacks. Added some explanations. – Introit

Answer 7

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

Tapis

Answer 8

This will provide starting date of an Indian financial year. i.g. April to March.

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

Belay

Answer 9

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

Kalisz