Reputation: 531
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
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
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
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
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
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
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
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
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
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
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
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