UFN_GLFISCALYEAR_GETYEARFROMDATE

CREATE function dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE
(
  @DATE datetime
)
returns datetime
with execute as caller
as
begin
  declare @FISCALDATE datetime;

  --Check table exists since this FN is platform and that BasicGL is installed

  if object_id(N'dbo.GLFISCALPERIOD', N'U') is not null and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0e85c527-e6e9-4c5f-a8e8-105fd0e18fe7') = 1
  begin
    declare @TODAYDATE date = cast(@DATE as date);

    --Set Fiscal date based on Fiscal start date - if it starts before last quarter then returns First day of the Fiscal year otherwise last day of the Fiscal year

    select  @FISCALDATE = case when DATEPART(month, T1.STARTDATE) < 10
              then T1.STARTDATE else T1.ENDDATE
            end
        from
        (
          select  (Select MIN(STARTDATE) from GLFISCALPERIOD as S1 where GLFISCALYEAR.ID=S1.GLFISCALYEARID ) as STARTDATE,
                  (Select MAX(ENDDATE) from GLFISCALPERIOD as S1 where GLFISCALYEAR.ID=S1.GLFISCALYEARID ) as ENDDATE
          from GLFISCALYEAR 
        ) T1
        where @TODAYDATE between T1.STARTDATE and T1.ENDDATE
  end
  else
  begin
    declare @FISCALYEARFINALMONTH tinyint;
    select
        @FISCALYEARFINALMONTH = FISCALYEARFINALMONTH
    from
        dbo.INSTALLATIONINFO
    where
        ID = 1;

    set @FISCALDATE = dateadd(month, (@FISCALYEARFINALMONTH + 12 - month(@DATE)) % 12 - 11, dbo.UFN_DATE_THISMONTH_FIRSTDAY(@DATE, 0));

    if datepart(month, @FISCALDATE) >= 10
        set @FISCALDATE = dateadd(year, 1, @FISCALDATE);

  end

  return @FISCALDATE;
end