How to Convert Datareader Result of DbType.Time to Timespan Object?

Question

I am reading a result from a MS SQL 2008 Database with a column type of dbtype.time from a datareader, using c# with DAAB 4.0 framework.

My problem is the MSDN docs say dbtype.time should map to a timespan but the only close constructor for timespan I see accepts a long, and the result returned from the datareader cannot be cast to a long, or directly to a timespan.

I found this Article whichs shows datareader.getTimeSpan() method, but the datareader in daab 4.0 does not seem to have this method.

So how do I convert the result from the datareader to a timespan object ?

score 11 · Answer 1 · answered Feb 26 '09 at 20:08

11

Have you tried a direct cast like this?

TimeSpan span = (TimeSpan)reader["timeField"];

I just tested this quickly on my machine and works fine when "timeField" is a Time datatype in the database (SQL).

answered Feb 26 '09 at 20:08

BFree

102,548
21
159
201

Both yours and Ken's solutions are good. However, if the value is null, Ken's solution's exception message is more descriptive. `SqlNullValueException - Data is Null. This method or property cannot be called on Null values` VS `InvalidCastException - Specified cast is not valid`. – actaram May 05 '15 at 15:28
2

To answer BishopBarber comment if the column can be nullable you should convert it to TimeSpan? nullable type and check for null. `TimeSpan? span = reader["tsfield"] == DBNull.Value ? (TimeSpan?) null : (TimeSpan?) reader["tsfield"]` – Arno 2501 Jun 21 '16 at 07:39
I found this solution better because I often don't access my data reader by index. I do however have an intermediate method that checks for null. – MichaelChan Sep 06 '17 at 03:15

Ken Browning · Accepted Answer · 2009-02-26T20:33:27.390

GetTimeSpan is a method of OleDbDataReader and SqlDataReader (but not of the more generic IDataReader interface which DAAB's ExecuteReader returns). I'm assuming that the IDataReader instance which DAAB has returned to you is actually an instance of SqlDataReader. This allows you to access the GetTimeSpan method by casting the IDataReader instance appropiately:

using (IDataReader dr = db.ExecuteReader(command))
{
    /* ... your code ... */
    if (dr is SqlDataReader)
    {
        TimeSpan myTimeSpan = ((SqlDataReader)dr).GetTimeSpan(columnIndex)
    }
    else
    {
        throw new Exception("The DataReader is not a SqlDataReader")
    }
    /* ... your code ... */
}

Edit: If the IDataReader instance is not a SqlDataReader then you might be missing the provider attribute of your connection string defined in your app.config (or web.config).

score 1 · Answer 3 · edited Nov 30 '12 at 02:36

1

Here's my take:


using (IDataReader reader = db.ExecuteReader(command))
{
    var timeSpan = reader.GetDateTime(index).TimeOfDay;
}

Cleaner, perhaps!

edited Nov 30 '12 at 02:36

Mxyk

10,678
16
57
76

answered Feb 09 '11 at 18:21

Jason Steele

11
1

The database layer throws an `InvalidCastException` for me when it returns a `TIME` type but I try to read with `GetDateTime()`. – binki Oct 28 '15 at 04:23

score 0 · Answer 4 · answered Feb 26 '09 at 20:05

0

What is the .NET type of the column value? If it is a DateTime then you can pass the value of its Ticks property (long) to the TimeSpan constructor. E.g.

var span = new TimeSpan(colValue.Ticks);

answered Feb 26 '09 at 20:05

Adam Ralph

29,453
4
60
67

How to Convert Datareader Result of DbType.Time to Timespan Object?

4 Answers4