CODEWITHSUNDEEP
sql-server
dazzathedrummer
dazzathedrummer

Reputation: 531

SQL Server - how to dynamically determine financial year?

Every year I have to update my company's financial reports to include the new financial year (as the year isn't coterminus with the calendar year), so I do.....

Case
    when ST_date >= '1996.11.01 00:00:00' and st_date  < '1997.11.01 00:00:00' 
    then '96-97'
[etc]
end as year,

Every year I have to remember which reports I need to amend - most years I forget one!

...Is there a simple dynamic way to determine this?

Upvotes: 2

Views: 37451

Answers (11)

Chandrakala
Chandrakala

Reputation: 1

--TO FIND FINANCIAL YEAR INPUT--> 2023-03-31 OUTPUT-->2022-03-31

Declare @cur_date Date,@DATE1 DATE, @DATE2 DATE

Set @cur_date ='2023-03-31'
 SET @DATE1= TRY_CAST((CAST (YEAR(@cur_date) AS VARCHAR)+'/04'+'/30') AS DATE)
 SET @DATE2=TRY_CAST(CAST(YEAR(DATEADD(YEAR,1,@cur_date)) AS VARCHAR)+'/03'+'/31' AS DATE)

--select @CUR_DATE,@date1,@date2



SELECT CASE WHEN @cur_date BETWEEN @DATE1 AND @DATE2 THEN 
                  TRY_CAST((CAST (YEAR(@cur_date) AS VARCHAR)+'/03'+'/31') AS DATE)
            
            WHEN @cur_date < @DATE1 
            THEN TRY_CAST(CAST(YEAR(DATEADD(YEAR,-1,@cur_date)) AS VARCHAR)+'/03'+'/31' 
                 AS DATE)
                 
      END

Upvotes: 0

Santanu Maity
Santanu Maity

Reputation: 1

CREATE FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    DECLARE @YearOfDate INT

    IF (MONTH(@input) >= 4)
        SET @YearOfDate = YEAR(@input)  
    ELSE
        SET @YearOfDate = YEAR(@input) - 1

    SET @FinYear = RIGHT(CAST(@YearOfDate AS CHAR(4)), 2) + '-' + RIGHT(CAST((@YearOfDate + 1) AS CHAR(4)), 2)

    RETURN @FinYear
END

Upvotes: 0

Hasanur Rahaman Sikder
Hasanur Rahaman Sikder

Reputation: 1

declare @dt datetime
declare @year1 numeric(4)
declare @year2 numeric(4)

set @dt='2024-09-01'  -- Any Date

select @year1= case when month (@dt)<=6 then year (@dt)-1 when month (@dt)>6 then year (@dt) end
select @year2= case when month (@dt)<=6 then year (@dt) when month (@dt)>6 then year (@dt)+1 end

select @year1,@year2

Upvotes: 0

Deepali
Deepali

Reputation: 1

Declare @date1 datetime = '2017-07-01'
Select Case 
    When Month(@date1)>=7 Then 'FY'+Convert(NVARCHAR(10),(Right(year(getdate()),2)+1))
    Else 'FY'+Convert(NVARCHAR(10),(Right(year(getdate()),2)))
End

Upvotes: 0

Krishna yadav
Krishna yadav

Reputation: 1

Create FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    IF (MONTH(@input) > 3)
        SET @FinYear = RIGHT(CAST(Year(@input) AS CHAR(4)), 4) + '-' + RIGHT(CAST((Year(@input) + 1) AS CHAR(4)), 2)
    ELSE
        SET @FinYear = RIGHT(CAST((Year(@input) - 1) AS CHAR(4)), 4) + '-' + RIGHT(CAST(Year(@input) AS CHAR(4)), 2)

    RETURN @FinYear
END

Upvotes: 0

sandipmatsagar
sandipmatsagar

Reputation: 35

datename(YEAR,  DATEADD(M,-3,Date)) +'-'+ cast((datepart(YEAR,  DATEADD(M,-3,Date)) + 1) %100 as varchar(2))

Calculate on Column 'Date'

Financial year ranges from 1st April to 31st March

Upvotes: 0

Stuyvie
Stuyvie

Reputation: 1

This one works for me and sets it as the actual FY end date.

SET @enddatefy = convert(DATE, str(datepart(yyyy,DateAdd(m,-6,@enddate))+1)+'0630',112) SET @enddatefyid = str(datepart(yyyy,DateAdd(m,-6,@enddate))+1)+'0630'

Upvotes: 0

Ben
Ben

Reputation: 1

This works for me, where the financial year starts in July.

CASE WHEN DatePart(mm, [YourDate]) >= 7 
  THEN convert(varchar(10), YEAR([YourDate])) +' / '+ Convert(varchar(10), YEAR([YourDate]) + 1 )
  ELSE Convert(varchar(10), YEAR([YourDate]) - 1) +' / '+ Convert(varchar(10), YEAR([YourDate]) )
  END AS [Financial Year],

Upvotes: -1

venkat
venkat

Reputation: 11

Declare @FinancialMonth Varchar(100)=NULL,@Month smallint,@Date DateTime='04/06/2013'

BEGIN TRY 
SELECT @FinancialMonth='01-'+IsNULL(@FinancialMonth,'April')+'-'+Cast(year(getdate()) as varchar)
SELECT @Month=(Month(Cast(@FinancialMonth as datetime))-1) * -1
END TRY
BEGIN CATCH
SELECT 
        ERROR_NUMBER() AS ErrorNumber,'Invalid Financial Month' ErrorMessage
END CATCH

SELECT Month((CONVERT([varchar](10),dateadd(month,(@Month),@Date),(101)))) FinancialMonth,
Year((CONVERT([varchar](10),dateadd(month,(@Month),@Date),(101)))) FinancialYear
,DatePart(qq,(CONVERT([varchar](10),dateadd(month,(@Month),@Date),(101)))) FinancialQuarter

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

Have a look at this example:

declare @ST_Date datetime = '20120506'

SELECT
    convert(char(2),DateAdd(m,-10,@ST_DATE),2)+'-'+
    convert(char(2),DateAdd(m,+ 2,@ST_DATE),2) as year

As a column expression:

    convert(char(2),DateAdd(m,-10,ST_DATE),2)+'-'+
    convert(char(2),DateAdd(m,+ 2,ST_DATE),2) as year

Pretty trivial!

The way I handle these problems (financial year, pay period etc) is to recognize the fact that financial years are the same as any year, except they start X months later. The straightforward solution is therefore to shift the FY by the number of months back to the calendar year, from which to do any "annual" comparisons or derivation of "year" (or "month").

Upvotes: 3

marc_s
marc_s

Reputation: 755541

You could definitely write a simple stored function in SQL Server to determine the financial year based on the date:

CREATE FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    SET @FinYear = 
        CASE
            WHEN @INPUT >= '19961101' AND @input < '19971101' THEN '96-97'
            WHEN @INPUT >= '19971101' AND @input < '19981101' THEN '97-98'
            ELSE '(other)'
        END

    RETURN @FinYear
END

and then just use that in all your queries. 

SELECT 
    somedate, dbo.GetFinancialYear(somedate)
......

If you need to add a new financial year - just update the one function, and you're done !

Update: if you want to make this totally dynamic, and you can rely on the fact that the financial year always starts on Nov 1 - then use this approach instead:

CREATE FUNCTION dbo.GetFinancialYear (@input DATETIME)
RETURNS VARCHAR(20)
AS BEGIN
    DECLARE @FinYear VARCHAR(20)

    DECLARE @YearOfDate INT

    IF (MONTH(@input) >= 11)
        SET @YearOfDate = YEAR(@input)  
    ELSE
        SET @YearOfDate = YEAR(@input) - 1

    SET @FinYear = RIGHT(CAST(@YearOfDate AS CHAR(4)), 2) + '-' + RIGHT(CAST((@YearOfDate + 1) AS CHAR(4)), 2)

    RETURN @FinYear
END

This will return:

  • 05/06 for a date such as 2005-11-25
  • 04/05 for a date such as 2005-07-25

Upvotes: 6

Related Questions