What is the correct SQL type to store a .Net Timespan with values > 24:00:00?

Question

I'd store it in the database as a BIGINT and I'd store the number of ticks (eg. TimeSpan.Ticks property).

That way, if I wanted to get a TimeSpan object when I retrieve it, I could just do TimeSpan.FromTicks(value) which would be easy.

Thanks for the advice. As there is no equivalent in SQL server. I simply created a 2nd field which converted the TimeSpan to ticks and stored that in the DB. I then prevented storing the TimeSpan

public Int64 ValidityPeriodTicks { get; set; }

[NotMapped]
public TimeSpan ValidityPeriod
{
    get { return TimeSpan.FromTicks(ValidityPeriodTicks); }
    set { ValidityPeriodTicks = value.Ticks; }
}

If you don't have to store more than 24 hours you can just store time, since SQL Server 2008 and later the mapping is

time (SQL Server) <-> TimeSpan(.NET)

No conversions needed if you only need to store 24 hours or less.

Source: http://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx

But, if you want to store more than 24h, you are going to need to store it in ticks, retrieve the data and then convert to TimeSpan. For example

int timeData = yourContext.yourTable.FirstOrDefault();
TimeSpan ts = TimeSpan.FromMilliseconds(timeData);

There isn't a direct equivalent. Just store it numerically, e.g. number of seconds or something appropriate to your required accuracy.