My previous post showed a way to get around the limitations of the DateDiff function by creating a .NET CLR function that could cope with returning values between dates that were far apart.  I’ve now taken this a step further by creating a SQL .NET CLR User Defined Type which represents a TimeSpan object. The object has basic Add and Subtract methods which utilise an underlying C#  TimeSpan object.  The object is byte orders and all methods to create it are deterministic meaning that it can be used in a persisted computed column. First of all start with a SQL CLR project.  Then add a new User Defined Type. The important methods are: IsNull, ToString(), Parse(string) and FromTicks(bigint). [Serializable][Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native, IsByteOrdered=true, IsFixedLength=true)]public struct StsTimeSpan : INullable{    private SqlInt64 tsValue;    private bool isNull;     public override string ToString()    {        if (!isNull)            return new TimeSpan((long)tsValue.Value).ToString();        else            return null;    }     public bool IsNull    {        get        {            return isNull;        }    }     public static StsTimeSpan Null    {        get        {            StsTimeSpan h = new StsTimeSpan();            h.isNull = true;            return h;        }    }     public static StsTimeSpan Parse(SqlString s)    {        if (s.IsNull)            return Null;        StsTimeSpan u = new StsTimeSpan();        u.isNull = false;        u.tsValue = long.Parse(s.ToString());        return u;    }     public static StsTimeSpan FromTicks(SqlInt64 ticks)    {        if (ticks.IsNull)            return Null;        StsTimeSpan u = new StsTimeSpan();        u.isNull = false;        u.tsValue = ticks;        return u;    }     public StsTimeSpan AddTicks(SqlInt64 ticks)    {        if (ticks.IsNull || isNull)            return Null;        StsTimeSpan u = new StsTimeSpan();        u.isNull = false;        u.tsValue = this.tsValue + ticks;        return u;    }     public StsTimeSpan Add(StsTimeSpan timeSpan)     {         if (timeSpan.IsNull || isNull)             return Null;         StsTimeSpan u = new StsTimeSpan();         u.isNull = false;         u.tsValue = this.tsValue + timeSpan.tsValue;         return u;     }     public StsTimeSpan AddDays(int days)    {        if (isNull)            return Null;        StsTimeSpan u = new StsTimeSpan();        u.isNull = false;        u.tsValue = new TimeSpan((long)tsValue).Add(new TimeSpan(days, 0, 0, 0)).Ticks;        return u;    }     public SqlInt64 ? Ticks    {        get        {            if (isNull)                return null;            return tsValue;        }    }     public SqlDecimal ? TotalSeconds    {        get        {            if (isNull)                return null;            return (SqlDecimal) new TimeSpan((long)tsValue).TotalSeconds;        }    }     public TimeSpan ? TimeSpan    {        get        {            if (isNull)                return null;            return new TimeSpan((long)tsValue);        }    }} In a separate User Defined Functions class create a method which is deterministic and created an instance of the timespan class public partial class UserDefinedFunctions{    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]    public static StsTimeSpan ToTimeSpanFromTicks(SqlInt64 ticks)    {        return StsTimeSpan.FromTicks(ticks);    }     [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]    public static StsTimeSpan ToTimeSpanFromDates(DateTime from, DateTime to)    {        return StsTimeSpan.FromTicks(from.Subtract(to).Ticks);    }}   In You can’t overload methods which are going to be used as Sql Functions.  you won’t get any errors but they won’t deploy to your database. Click build and then deploy. Now in Sql, create a new table.  In my example I am showing the time span object being used in a persisted computed column which is generated by calling the UDF ToTimeSpanFromTicks.   CREATE TABLE [dbo].[TimeSpanTest](       [Ticks] [bigint] NOT NULL,       [Length]  AS ([dbo].[ToTimeSpanFromTicks]([Ticks])) PERSISTED ) ON [PRIMARY]   GO If in this table you create a new row with a ticks value of 600, 000, 000 (which is 1 minute, there are 100 nanoseconds in 1 tick, hence 10, 000, 000 ticks in a second). You then get a time span object showing 00:01:00 1 minute. SELECT TOP 1000 [Ticks]  ,[Length]       ,[Length].ToString()   FROM [dbo].[TimeSpanTest] The To String method call is needed to change the byte representation of the CLR object into text. Ticks Length (No column name) 600000000 0x018000000023C3460000 00:01:00 And that’s it. A CLR object representing a .NET timespan object.

The built in SQL dateDiff function has issues when trying to calculate the difference between dates that are far apart when you request a high precision. There are solutions available which request a difference with a lower precision and multiply that by a constant, however I was looking for a more absolute answer, so I turned to the .Net runtime. [More]