SQL: Get Current Financial Year dynamically in Select Statement.

Sometime, we are asked to write a query where we have to get a result set based on current financial year. Let's say my financial year begin 1st of November ever year and end 31st October.

And we have to do a select between these two date ranges.

Here's how we do it.

Select * from mytable
where myfinancialdate >= CASE WHEN month(getdate()) > 11 
THEN convert(datetime, cast(year(getdate()) as varchar) + '-11-1')
ELSE convert(datetime, cast(year(getdate()) - 1 as varchar) + '-11-1') end 
AND (myfinancialdate <= CASE WHEN month(getdate()) > 11 
THEN convert(datetime, cast(year(getdate()) + 1 as varchar) + '-10-31')
ELSE convert(datetime, cast(year(getdate()) as varchar) + '-10-31')  end);


This will result set only following between current financial year. For example in 2014 year, the result will bring value from 11-1-2013 to 10-31-2014.

Let say that we want to retrieve all the data from last fiscal year and current fiscal year. To do this, we can use this approach:

Let's declare some variable.

--Fiscal year variables
, @Now datetime = getdate()
, @CurrentFiscalYear_BeginDate date
, @PastFiscalYear_BeginDate date
, @CurrentFiscalYear_EndDate date

--Set Fiscal year values
select @CurrentFiscalYear_BeginDate = '11/1/' + cast(case when month(@Now) > 11 then year(@Now) else year(@Now)-1 end as varchar(4))

select
 @PastFiscalYear_BeginDate = dateadd(year, -1, @CurrentFiscalYear_BeginDate) --decrement year of current FY begin date to get past FY begin date
, @CurrentFiscalYear_EndDate = dateadd(day, -1, dateadd(year, 1, @CurrentFiscalYear_BeginDate)) --increment year of current FY begin date to get next FY begin date and then subtract one day to get end date of current FY
       

Now we can use these variable to get data.

Select * from mytable
Where myfinancialdate 
between @PastFiscalYear_BeginDate AND @CurrentFiscalYear_EndDate


This will retrieve all the row between last fiscal year and current fiscal year.