Calculate DateTime Difference in SQL Server

I have no idea, why still SQL Server have no support for TimeSpan type. Also in 2008 version will be so many new DateTime types but still functions for manipulating DateTime values are few. If you need to calculate difference between two dates, but that difference to be represented as years:months: days, you will realize that that is not an easy task. There is DateDiff function, but when you try to use it you will see that this function will not return desired result. At least to get the result in a easy way.

I was in a need for such a function so I start looking for on the net. I found one which was pretty much what i needed. However I decide to create my own, much simpler and just for what I need. So here it is:

CREATE FUNCTION [dbo].[TotalDateDiff]
(
        @DateFrom AS SMALLDATETIME,
        @DateTo AS SMALLDATETIME
)
RETURNS CHAR(8)
AS
BEGIN
    DECLARE @Result CHAR(8)
    DECLARE @Years SMALLINT, @Months SMALLINT, @Days SMALLINT

    SELECT    @Years = DATEDIFF(YEAR, @DateFrom, @DateTo),
            @Months = DATEDIFF(MONTH, @DateFrom, @DateTo)

    SET @Months = @Months - @Years * 12

    SET @Days = DAY(@DateTo) - DAY(@DateFrom)
    IF @Days < 0
        BEGIN
            SET @Months = @Months - 1
            SET @Days = DAY(DATEADD(DAY, -DAY(@DateTo), @DateTo)) + @Days
        END

    IF @Months < 0
        BEGIN
            SET @Years = @Years - 1
            SET @Months = 12 + @Months
        END

    SET @Result = RIGHT('0' + CAST(@Years AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Months AS VARCHAR(2)), 2) + ':' +
                  RIGHT('0' + CAST(@Days AS VARCHAR(2)), 2)
    RETURN @Result
END

Visual C# MVP Award

Today I receive mail from Microsoft:

Congratulations! We are pleased to present you with the 2008 Microsoft® MVP Award! The MVP Award is our way to say thank you for promoting the spirit of community and improving people’s lives and the industry’s success every day. We appreciate your extraordinary efforts in Visual C# technical communities during the past year.

Thanks Microsoft, I really appreciate this award.

My efforts in past couple of years on msdn forums, in particular C#, ADO, SQL forums, for helping other developers is the main reason. My nickname is boban.s, so you can find some very useful posts from ones about base class libraries in .NET though posts related with windows application type of problems to threading, localization, application update etc.

You probably know that winning MVP award is a result of many activities in public community, writing books, blogs, managing user groups, etc. I would like to make this blog active in next year in order to get award for next year too. I will post mainly about C#, but also about T-SQL and SharePoint. I already have in mind what will be the next post.