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?

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

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