Do you ever get tired of building complicated date logic in your EDW queries?
The EDW now supports several finance date functions to help you simplify your queries! These functions help enterprise data users to easily convert calendar dates, months, and years to UW fiscal calendar objects. These functions are most helpful to SQL developers working on reports and visualizations that use fiscal calendar parameters/variables.
Context:
In SQL, a function is a program that takes some input and generates a value. SQL has many built-in functions that you get out-of-the-box. For example, one of the built-in system functions is GETDATE(). This function returns the current date and does not require an input or argument. Another example of a system function is REPLACE(), which expects to have 3 inputs, 1. The expression you want to search (i.e. column or field), 2. what you are searching for, and 3. what you want to replace it with.
The UW-IT EDW team recently added 5 new functions to the EDW. The following functions are now available to use:
| Function and Syntax | Description |
| FiscalYear(date) | Returns the fiscal year as a number based on the date input you provide. |
| FiscalMonth(date) | Returns the fiscal month as a 2-character string based on the date input you provide. |
| BienniumYear(date) | Returns biennium year as a number based on the date input you provide. |
| BienniumMonth(date) | Returns biennium month as a 2-character string based on the date input you provide. |
| LastMonthEndDate(date) | Returns last date of the prior calendar month based on the date input you provide. |
How you can use these functions today:
If you ever do your own calculations in SQL to determine things like Fiscal Year or Biennium start or end dates, or create a rolling window, then you might benefit from using these functions.
These functions reside in EDWPresentaton database. To call a function simply follow examples below:
SELECT
EDWPresentation.dbo.FiscalYear('4/4/2018') as FiscalYear,
EDWPresentation.dbo.FiscalMonth('4/4/2018') as FiscalMonth,
EDWPresentation.dbo.BienniumYear('4/4/2018') as BienniumYear,
EDWPresentation.dbo.BienniumMonth('4/4/2018') as BienniumMonth,
EDWPresentation.dbo.LastMonthEndDate('4/4/2018') as LastMonthEndDatePlease Note:
- When functions are used as parameters in a report or visualization, they should be set as variables.
- Also, the BienniumMonth function returns values ranging from 01-24; however, UW accounting months are 01-25 or in previous years may even have fallen between 01-27. This happens because some transactions are entered in a new biennium, but should be attributed to the prior one. Examples below show how to use functions in variables and how to handle accounting months
Examples
SQL – if parameters are start and end dates
--Report parametersDECLARE @MyDate date = '4/4/2018'DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)DECLARE @AcctngMonth int = case when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' then '27' else EDWPresentation.dbo.BienniumMonth(@MyDate) endDECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth
SQL – if parameters are month and year
--Report parametersDECLARE @calmonth int = 9DECLARE @calyear int = 2017--Converting calendar month and year to a date that can be used by functionsDECLARE @MyDate date = convert(date, convert(varchar(4),@calyear) + '-' + convert(varchar(2), @calmonth) + '-01')DECLARE @FiscalYear varchar(4) = EDWPresentation.dbo.FiscalYear(@MyDate)DECLARE @AcctngMonth int = case when EDWPresentation.dbo.BienniumMonth(@MyDate) = '24' then '27' else EDWPresentation.dbo.BienniumMonth(@MyDate) endDECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth
SQL – rolling window (2 years back from end date)
--Report parametersDECLARE @EndDate date = '4/4/2018'--Converting end date into a 2-year rolling windowDECLARE @LastMonthEndDate date = EDWPresentation.dbo.LastMonthEndDate(@EndDate)DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@LastMonthEndDate)DECLARE @AcctngMonth varchar(2) = case when EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) = '24' then '27'else EDWPresentation.dbo.BienniumMonth(@LastMonthEndDate) endDECLARE @EndRollingPeriod varchar(6) = @BienniumYear + @AcctngMonthDECLARE @StartRollingPeriod varchar(6) = CONVERT(varchar(4), CONVERT(int, @BienniumYear)-2) + @AcctngMonthSELECT @StartRollingPeriod, @EndRollingPeriod
Questions:
If you have any questions about functions or how to use them in your solution, please write help@uw.edu and put “EDW Feature: Finance Date Functions” in the subject line.
