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?
39 Answers
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 For SQL server 2012+ versions, you can use DATEFROMPARTS
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 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]; 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 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 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; 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 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) 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 
