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.

SMA Technical Indicator

I saw a question on MSDN Forums about having real-time SMA indicator. So even if I never used this indicator in real systems, I know it's simplest one and decide to develop it. So here it is:

public class SMA
{
    private readonly int _Length;
    private readonly bool _StoreData;
    private decimal _Value;
    private decimal _Price;
    private bool _Primed;
    private readonly string _Name;
    private readonly DecimalCollection _PriceArray = new DecimalCollection();
    private readonly DecimalCollection _ValueArray = new DecimalCollection();

    public SMA(int length) : this(length, false)
    {
    }

    /// <summary>
    /// Class for calculating Simple Moving Average
    /// </summary>
    /// <param name="length">Lenght SMA calculation formula</param>
    /// <param name="storeData"></param>
    public SMA(int length, bool storeData)
    {
        _Length = length;
        _StoreData = storeData;
        _Name = GetType().Name + Length;
    }

    public int Length
    {
        get { return _Length; }
    }

    public bool StoreData
    {
        get { return _StoreData; }
    }

    public decimal Value
    {
        get { return _Value; }
    }

    public bool Primed
    {
        get { return _Primed; }
    }

    public string Name
    {
        get { return _Name; }
    }

    public DecimalCollection ValueArray
    {
        get { return _ValueArray; }
    }

    public void PriceTick(decimal price, bool add)
    {
        if (add)
            AddPrice(price);
        else
            EditPrice(price);
    }

    public void PriceTicks(DecimalCollection prices)
    {
        if (prices == null || prices.Count == 0)
            return;
        for (int i = 0; i < prices.Count; i++)
        {
            AddPrice(prices[i]);
        }
    }

    private void AddPrice(decimal price)
    {
        _Price = price;
        if (!_Primed)
        {
            _PriceArray.Add(_Price);
            if (_PriceArray.Count == Length)
            {
                _Primed = true;
                _Value = _PriceArray.Average();
            }
        }
        else
        {
            _PriceArray.RemoveAt(0);
            _PriceArray.Add(price);

            _Value = _PriceArray.Average();

            if (_StoreData)
            {
                ValueArray.Add(_Value);
            }
        }

    }

    private void EditPrice(decimal price)
    {
        if (price != _Price)
        {
            _Price = price;

            _PriceArray[_PriceArray.Count - 1] = _Price;

            if (_Primed)
            {
                if (_PriceArray.Count == _Length)
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
                else
                {
                    _Value = _PriceArray.Average();
                    if (_StoreData)
                    {
                        ValueArray[ValueArray.Count - 1] = _Value;
                    }
                }
            }
        }
    }
}

This source uses DecimalCollection class that is already published on my blog.