Use SQL Server time datatype in C#.NET application?

Question

How does one use the SQL time datatype introduced in SQL Server 2008 in C#.NET?

I've been trying to get it to work but no success.

Solution 1

Here is an MSDN article that reviews all the new Date and Time datatypes introduced in SQL Server 2008 with respect to ADO.NET. As the document says: For System.Data.DbType.Time you would use the .NET Framework type System.TimeSpan

Solution 2

I think you can use TimeSpan datatype for your purpose. Here is an article that explains the use of Time datatype in ADO.NET.

Solution 3

Also even people from Microsoft tend to recommend mapping the sql datatype time to System.Timestamp I would not recommend doing so,

since the range of sql time is 00:00:00.0000000 - 23:59:59.9999999

wheras the range of System.TimeSpan is 10675199.02:48:05.4775808 - 10675199.02:48:05.4775807

which is just slightly different and can lead to nasty runtime out of range errors.

Solution 4

you can read it using datareader using something similar to following statement .

TimeSpan time = dr.GetTimeSpan(dr.GetOrdinal(“Time7FieldName”));

Solution 5

How are you accessing the data? Using Entity Frameworks, StoredProcedures etc.
If you have to pass a string representation of the date make sure that you do it in the format "yyyy-mm-dd hh:mm:ss" otherwise you will run the risk of the dd/mm/yyyy vs mm/dd/yyyy confusion. If you are using Entity Framework or DataSets then you should just pass the parameter a DataTime instance eg DateTime.Now

View more solutions

Comments

[rdk]

I'm using castle active record to access data

[Roberto]

What do you recommend?

[Michael Sander]

Depends on what kind of surroundings you have, I typically tend to store seconds or even milliseconds if needed.