fiscal year sql server

Question

I want to have a function that calculates the fiscal year. The fiscal year must begin on the first Monday in March. Thank you! example:

CREATE FUNCTION dbo.fnc_FiscalYear( @AsOf DATETIME ) 
RETURNS INT 
AS BEGIN 
DECLARE @Answer INT 
SET DATEFIRST 1 
IF ( MONTH(@AsOf) < 3 ) 
or MONTH(@AsOf=3) and datename(weekday, @AsOf) = 'Monday' and datepart(day, @AsOf)>=1 and datepart(day, @AsOf)<=7;
SET @Answer = YEAR(@AsOf) - 1 
ELSE SET @Answer = YEAR(@AsOf) 
RETURN @Answer 
END 
GO

but it's not working

Answer 1

It looks as though there are a number of syntax errors with your script. Try this instead. I've removed the SETs and returned at the point of the if statements. Also, note the grouping of the if statements.

CREATE FUNCTION dbo.fnc_FiscalYear( @AsOf DATETIME ) 
RETURNS INT 
AS BEGIN 
  DECLARE @Answer INT 
  IF (( MONTH(@AsOf) < 3 ) 
  OR (MONTH(@AsOf) = 3 
      AND DATENAME(weekday, @AsOf) = 'Monday' 
      AND datepart(day, @AsOf) >= 1 
      AND datepart(day, @AsOf)<=7))
    RETURN (YEAR(@AsOf) - 1)
  RETURN YEAR(@AsOf) 
END 
GO

Answer 2

The logic for this is tricky -- especially for the first week of March:

CREATE FUNCTION dbo.fnc_FiscalYear (
    @AsOf DATETIME
) 
RETURNS INT AS
BEGIN 
  RETURN( CASE WHEN MONTH(@AsOf) < 3 THEN YEAR(@AsOf) - 1
               WHEN MONTH(@AsOf) > 3 THEN YEAR(@AsOf)
               WHEN DAY(@AsOf) >= 7 THEN YEAR(@AsOf)
               WHEN DATENAME(@AsOf) = 'Monday' OR
                    DATENAME(@AsOf) = 'Tuesday' AND DAY(@AsOf) >= 2 OR
                    DATENAME(@AsOf) = 'Wednesday' AND DAY(@AsOf) >= 3 OR
                    DATENAME(@AsOf) = 'Thursday' AND DAY(@AsOf) >= 4 OR
                    DATENAME(@AsOf) = 'Friday' AND DAY(@AsOf) >= 5 OR
                    DATENAME(@AsOf) = 'Saturday' AND DAY(@AsOf) >= 26
               THEN YEAR(@AsOf)
               ELSE YEAR(@AsOf) - 1
          END);
END ;
GO

Answer 3

( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1 will always return an integer from 0 to 6 with 0 corresponding to Sunday regardless of the setting of DateFirst or Language and without string manipulation.

Tweaking the expression to ( @@DateFirst + DatePart( weekday, SampleDate ) - 2 ) % 7 shifts the start of the cycle from Sunday to Monday and the range to 0 to 6 which, in turn, simplifies the following code:

create function dbo.FiscalYear( @Date as Date )
  returns Int
  begin
  return Year( @Date ) - case
    when Month( @Date ) <= 2 then 1 -- January and February are always part of the prior year.
    -- In March it is the prior year only in the first week and if the calculated
    --   DoW is less than the day of the month.
    when Month( @Date ) = 3 and Day( @Date ) < 7 and
      ( @@DateFirst + DatePart( weekday, @Date ) - 2 ) % 7 >= Day( @Date ) then 1
    else 0 end;
  end;

Test the function with sample data:

with
  -- Generate sample dates for 20 years.
  YearOffsets as (
    select 0 as YearOffset
    union all
    select YearOffset + 1
      from YearOffsets
      where YearOffset < 20 ),
  SampleDates as (
    select Cast( DateAdd( year, YearOffset, '2000-02-28' ) as Date ) as SampleDate, 1 as Counter
      from YearOffsets
    union all
    select DateAdd( day, 1, SampleDate ), Counter + 1
      from SampleDates
      where Counter < 10 )
  -- Calculate the Fiscal Year for each sample date.
  select SampleDate, DateName( weekday, SampleDate ) as WeekDay,
    ( @@DateFirst + DatePart( weekday, SampleDate ) - 2 ) % 7 as DayOfWeek, -- 0 = Monday.
    dbo.FiscalYear( SampleDate ) as FiscalYear
    from SampleDates
    order by SampleDate;