Use Fiscal Years in SAP B1 SQL Queries

When writing a SQL report, nothing causes problems quite like Fiscal years. SQL assumes Calendar years for queries, making for wildly inaccurate reports. Fortunately, there's a way to get fiscal years using SQL and review some Date functions.?

For more on the date function, review the videos from SAP Business One reporting and customization. I'll be using Declare for this, which I covered in the last newsletter, so I'd suggest reading this first if you are not familiar with DECLARE.?

For those not familiar with the problem of Fiscal years, these are yearly cycles that do not follow the Calendar year of January to December. Many companies will start their financial year in a different month than December. Retail, for example, often begins on February 1 to include the entire holiday buying season, which extends into the first week of January.

One method of dealing with the problem is to let the user put the correct date in using parameters. You'll often see WHERE clauses like this:

WHERE T0.DocDate>= [%0] AND T0.DocDate<=[%1]        

For many situations, this works perfectly fine. However, it has a few drawbacks:?

• You have a user who doesn't know the fiscal start date.?
• A user can pick any two dates, making for some inaccurate reports or reports you don't want users to see.?
• ?The query is for a dashboard, and you can't use parameters.?

For this situation, computing the Fiscal start is a better solution.?

Current Calendar Date

If I wanted the first day of the current calendar year, I could use SQL date functions to get this:?

DECLARE @CalendarYearBegins DATETIME = CAST(YEAR(GETDATE()) as NVARCHAR(4)) +'0101'        

This code exploits SQL conversion rules that implicitly convert a valid string in a data form to a date. In this case, a date in the format of YYYYMMDD. Based on today's date, I Find the Current year With the YEAR function. I make that a string and then concatenate the first month and the first day of the year.?

I can get the end date based on this date. I add 12 months to get the beginning of the next fiscal year and then subtract one day.?

DECLARE @CalendarYearEnds DATETIME = DATEADD(DAY,-1,DATEADD(MONTHS,12,@calendarYearBegins))        

Fiscal Years

However, fiscal years are not as easy. The problem is twofold:?

• We do not use '0101' for the first day of the fiscal year
• Months where the current date is in the previous fiscal year

For example, If I have an April 1 fiscal year start and my current date is 5/15/22, the fiscal year starts on 20220401, but if my current date is 2/15/22, the fiscal date starts 20210401.??

I'll use some constants to make things a little easier to keep track to set this up.?

--basic Constants

DECLARE @Today DATETIME = '20220215' -- Replace with GetDate()

DECLARE @FiscalStartMonth INT = 4?        

While building and testing, I'll use a literal to specify dates, but I'll change that to a GETDATE() function in production.?

I specify my month as a number, with 1 for January and 12 for December.?

The first element I'll figure out is the year. If the current month is less than the Fiscal start month, I'll subtract one from the year. Otherwise, I'll use the current year. I cast that to a string to concatenate it to my finished date.?

DECLARE @StartFiscalYear NVARCHAR(4) = CAST( Case WHEN MONTH(@Today) < @FiscalStartMonth THEN YEAR(@Today) - 1 ELSE YEAR(@Today) END AS NVARCHAR(4))        

The second part of my date is the start month. I used an integer for this, so I have to make a string with leading zeroes. While there are ways with FORMAT to do this, a simple string function will be a bit faster.?

DECLARE @StartFiscalMonthDay NVARCHAR(4)= RIGHT ( '00' + CAST (@FiscalStartMonth as NVARCHAR(2)), 2)?        

I concatenate the digit after I cast it to a string with a mask of 00. This code gives us a string with more than two digits, and I take the right two digits as my number, which adds the leading zeroes.?

Then I put all that together to get my fiscal year.?

DECLARE @FiscalBeginDate DATETIME = @StartFiscalYear + @StartFiscalMonth + '01'        

I can look at my finished date like this:

SELECT @today, @FiscalBeginDate        

with the February 15, 2022 date, I should see a calendar date of 2022, and a fiscal date of 2021

Change @Today to May:

DECLARE @TODAY DATETIME = '20220515' -- Replace with GetDate()        

When I execute, I get a 2022 date:

I can, of course, add date calculations to this to get fiscal end and previous fiscal years.?

DECLARE @FiscalEndDate DATETIME = DATEADD(DAY, -1,DATEADD(MONTH,12,@FiscalBeginDate))

DECLARE @FiscalBeginPreviousYear DATETIME = DATEADD(YEAR, -1, @FiscalBeginDate)

DECLARE @FiscaEndlPreviousYear DATETIME = DATEADD(YEAR, -1, @FiscalEndDate)        

Late Q2 and Q3 Fiscal Date Variation

You might come across one variation in companies that have fiscal dates starting late in the calendar year. A September 1 fiscal start in 2022 might be the start of the 2023 fiscal year. For those cases, you'll change around the @startFiscalYear to this:?

DECLARE @StartFiscalYear NVARCHAR(4) = CAST( Case WHEN MONTH(@Today) < @FiscalStartMonth THEN YEAR(@Today) - 1 ELSE YEAR(@Today) + 1 END AS NVARCHAR(4))        

Try this out with a the May 15 Date

And then September 15 .?

No matter what your fiscal year structure, you can now set your queries to see only the fiscal year to date

WHERE t0.DocDate >= @FiscalBeginDate AND t0.DocDate <= @today        

or the entire year with?

WHERE t0.DocDate >= @FiscalBeginDate AND t0.DocDate <= @FiscalEndDate        

Fiscal years can be tricky. In these examples, we assume the company uses the calendar months. Some companies such as Apple use 28-day months, which sets up even more problems. But for the majority of cases having these statements in a file somewhere to add to queries will make your life creating reports much more manageable and accurat