We are proud to announce the release of pre-defined date functions in the EDW. 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 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 LastMonthEndDate
Please 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
SQL Examples:
If Parameters are start and end dates:
--Report parameters
DECLARE @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) end
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)
SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth
If Parameters are a month and year:
--Report parameters
DECLARE @calmonth int = 9
DECLARE @calyear int = 2017
--Converting calendar month and year to a date that can be used by functions
DECLARE @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) end
DECLARE @BienniumYear varchar(4) = EDWPresentation.dbo.BienniumYear(@MyDate)
SELECT @FiscalYear FiscalYear, @BienniumYear BienniumYear, @AcctngMonth AcctngMonth
If Parameters are a rolling window (2 years back from a given end date):
--Report parameters
DECLARE @EndDate date = '4/4/2018'
--Converting end date into a 2-year rolling window
DECLARE @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) end
DECLARE @EndRollingPeriod varchar(6) = @BienniumYear + @AcctngMonth
DECLARE @StartRollingPeriod varchar(6) = CONVERT(varchar(4), CONVERT(int, @BienniumYear)-2) + @AcctngMonth
SELECT @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: Date Functions” in the subject line
