Archive

Archive for March, 2009

Date functions

March 15th, 2009 3 comments

Almost every time I write a report, I face some date formating and calculation issues.

For once, dates stored in databases usually are in full “date” format, including year, month, day, hour, minutes and seconds, and are associated with a single action or event, such as creation, update, etc…

Then, the reports usually need the information aggregated by month, quarter and/or year, and often with a sliding begin and end date (i.e. count of the number of logged calls per month for the last 6 months).

On to top of this, the dates are not necessarily following a calendar year but often follows a fiscal year instead, and the reporting tools do not feature an easy way to provide “today’s fiscal quarter” for instance.

In order to help with this, I created 3 simple SQL Server user based functions:

The first function returns the fiscal year from any date (including today()):

[cc lang=”tsql” width=”560″]
CREATE FUNCTION FYear (@input_date varchar(255))
RETURNS int AS
BEGIN
DECLARE @CYear int
DECLARE @offset int
SELECT @CYear = Year( @input_date )
IF ( (CAST(YEAR(@input_date) as varchar(2))+ ‘/’ + (CAST((MONTH(@input_date)) as varchar(2)) + ‘/’ + CAST((DAY(@input_date)) as varchar(2)))) > (CAST(YEAR(@input_date) as varchar(2)) + ’06/30/’))
SELECT @offset =1
ELSE
SELECT @offset = 0
RETURN (@CYear + @offset)
END
[/cc]

Simply replace ’06/30/’ in the code by the end of your fiscal year, and this function will compare the date with the end of the fiscal year and return the current year or the current year + 1 based on that end.

The second function returns the fiscal quarter of a date:

[cc lang=”tsql” width=”560″]
CREATE FUNCTION FQuarter (@input_date varchar(255))
RETURNS varchar(255) AS
BEGIN
DECLARE @FQuarter varchar (255)
IF (Month(@input_date) < 4)
SELECT @FQuarter =  ‘Q3’
ELSE
IF (Month(@input_date) < 7)
SELECT @FQuarter =   ‘Q4’
ELSE
IF (Month(@input_date) < 10)
SELECT @FQuarter =   ‘Q1’
ELSE
SELECT @FQuarter =  ‘Q2’
RETURN @FQuarter
END
[/cc]

In this one, simply replace the ‘4’, ‘7’ and ’10’ values by the actual end of your fiscal quarters in order to assign the proper Qx to the result.

The last function returns a formated date as a string with a “YYY/MM/DD” format instead of a date field:

[cc lang=”tsql” width=”560″]
CREATE FUNCTION castyear (@input_date varchar(255))
RETURNS varchar(255) AS
BEGIN
DECLARE @CYear varchar(255)
SELECT @CYear = (CAST(YEAR(@input_date) as varchar(4)) + ‘/’ + CAST(MONTH(@input_date) as varchar(2)) + ‘/’ + CAST(DAY(@input_date) as varchar(2)) )
RETURN @CYear
END
[/cc]

Adding these function as user defined functions with proper access right to the database users can be useful to further chop the dates and easily extract the month and day of a date in order to create calculated fields and aggregate other facts based on dates.

Categories: Tech tips Tags: