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

.net sql-server timespan
GraemeMiller picture GraemeMiller · Dec 14, 2011 · Viewed 109.6k times · Source

I am trying to store a .Net TimeSpan in SQL server 2008 R2.

EF Code First seems to be suggesting it should be stored as a Time(7) in SQL.

However TimeSpan in .Net can handle longer periods than 24 hours.

What is the best way to handle storing .Net TimeSpan in SQL server?

Answer

Tom Chantler picture Tom Chantler · Dec 14, 2011

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.

Related questions

how to insert datetime into the SQL Database table?

How can I insert datetime into the SQL Database table ? Is there a way to insert this query through the insert command in C# / .NET?

Read More
C# Equivalent of SQL Server DataTypes

For the following SQL Server datatypes, what would be the corresponding datatype in C#? Exact Numerics bigint numeric bit smallint decimal smallmoney int tinyint money Approximate Numerics float real Date and Time date datetimeoffset datetime2 smalldatetime datetime time Character Strings …

Read More
How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and …

Read More