Menu
How would you calculate the fiscal year from a date field in a view in SQL Server?
980
This is a way to get current financial year using SQL query: DECLARE @FIYear VARCHAR(20) SELECT @FIYear = (CASE WHEN (MONTH(GETDATE())) <= 3 THEN convert(varchar(4), YEAR(GETDATE())-1) + '-' + convert(varchar(4), YEAR(GETDATE())%100)
According to the IRS, a fiscal year consists of 12 consecutive months ending on the last day of any month except December. 1 Alternatively, instead of observing a 12-month fiscal year, U.S. taxpayers may observe a 52- to 53-week fiscal year.
actually i calculate financial year like below in VB code. i just like the same in sql server query. "select * from admission where std_code=1 and fnyr in (select finy in system date)"<---i need here. Thanking you.
SQL does not really store "Partial Dates" (Month/Year without Day/Time). Since your fiscal month is always three months ahead of the calendar month, a calculated column will work well here.
I suggest you use a User-Defined Function based on the Fiscal year of your application.
CREATE FUNCTION dbo.fnc_FiscalYear(
@AsOf DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Answer INT
-- You define what you want here (September being your changeover month)
IF ( MONTH(@AsOf) < 9 )
SET @Answer = YEAR(@AsOf) - 1
ELSE
SET @Answer = YEAR(@AsOf)
RETURN @Answer
END
GO
Use it like this:
SELECT dbo.fnc_FiscalYear('9/1/2009')
SELECT dbo.fnc_FiscalYear('8/31/2009')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
