Home > Tech tips > Date functions

Date functions

March 15th, 2009 Leave a comment Go to 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:
  1. Sami
    July 8th, 2009 at 10:56 | #1
    Reply | Quote

    Hi Alex,

    I have also seen that client app’s local time zone plays a role. It would be great to figure out a scalable way to normalize timestamps to a universal time (some DBs do this but require config). From a reporting perspective it would be cool to normalize these either to local timezone (sales in UK on a given time in UK time) or global sales normalized to UTZ or EST for instance…

    Happy to see you’re enjoying the work!

  2. admin
    April 27th, 2009 at 08:10 | #2
    Reply | Quote

    Thanks Paul for the comments!
    You’re right on for a more scalable solution. I guess my solution’s benefit is that it requires less knowledge and DB rights when you’re not given permission to add new DB objects, but I certainly like your solution better for some long term, scalable needs.

  3. Paul Segal
    April 27th, 2009 at 01:57 | #3
    Reply | Quote

    Alex,
    This maybe a contentious statement, but the use of functions to calculate dates like this doesn’t scale very well, due to the sequential processing within the functions.
    A more scalable approach would be to define a lookup table and join to that. For example the fiscal quarter example you present, you could have a lookup table like this:

    Cal_Mth F_Qtr
    1 Q3
    2 Q3
    3 Q3
    4 Q4
    5 Q4
    6 Q4
    7 Q1
    8 Q1
    9 Q1
    10 Q2
    11 Q2
    12 Q2

    then have a statement like

    select fis_lkp.F_Qtr, count(cit.check_in_items)
    from db1.checkin_table cit
    inner join db2.fiscal_lookup fis_lkp
    on extract (month from cit.check_in_date) = fis_lkp.cal_mth

    The performance of this method will exceed that of the function when the large volumes of data are processed* as most optimisers will “know” that the lookup table is small and will load that small table into memory for use in the join.

    *This should be the case for the majority of the relational databases out there on the market.

    Likewise the fiscal year may be expressed as SQL as follows:

    select case when extract(month from check_in_date) in (1,2,3,4,5,6) then extract(year from check_in_date)-1 else extract(year from check_in_date) end
    from db1.checkin_table

    Naturally enough you can customise the statement after the then to produce whatever output you want, like, ‘FY’+cast(extract(year from check_in_date))-1) as char(4) which will produce FY2008 from 1/1/2009

    Also note that I have used ANSI SQL so it should be portable between RDBM’s.

  1. No trackbacks yet.