how to use sql server time data type in .net appli

Question

Does anybody knows how to use time data type introduced in sql server 2008 in c# .net application ? I've been trying to get it to work but no success.

Answer 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

Answer 2

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.

Answer 3

I think you can use TimeSpan data type for your purpose. Here is an article that explains the use of Time datatype in ado.net

Answer 4

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

Answer 5

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

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