C# - How To Retrieve SQL Server Time(7) Into TimeSpan

Question

I'm trying to understand how to retrieve time data from a SQL Server 2012 table column defined as Time(7). I've looked everywhere and have struggled with this. Here is some simple code that I'm using to just try to get it to work. Can you please help me??

When the code below runs (using Visual Studio 2013) I get the error on the TimeSpan DBStartTime... line:

Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.

I have no idea how to fix this.

var cnnString = ConfigurationManager.ConnectionStrings["TaktBoardsConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(cnnString); 

SqlCommand comm = new SqlCommand();
comm.CommandText = "SELECT * FROM ScheduleDetail WHERE ScheduleID = " + lstShifts.SelectedValue;
comm.CommandType = CommandType.Text;
comm.Connection = conn;

SqlDataReader reader;

conn.Open();
reader = comm.ExecuteReader(); 

while (reader.Read())
{
    TimeSpan DBStartTime = Convert.ToDateTime(reader["StartTime"]).TimeOfDay;
    TimeSpan DBEndTime = Convert.ToDateTime(reader["EndTime"]).TimeOfDay;

    // Add more coding once this works.
}

conn.Close();

Answer 1

Don't use Convert.ToDateTime the reader is already returning a Timespan, just do a direct cast.

    TimeSpan DBStartTime = (TimeSpan)reader["StartTime"];
    TimeSpan DBEndTime = (TimeSpan)reader["EndTime"];

---

Also, not related to your problem, but you are not using a parameter for ScheduleID = " + lstShifts.SelectedValue;, you really should. Nor are you using using statements, when your code does throw a exception you are not getting your connection objects closed.

var cnnString = ConfigurationManager.ConnectionStrings["TaktBoardsConnectionString"].ConnectionString;
using(SqlConnection conn = new SqlConnection(cnnString))
using(SqlCommand comm = new SqlCommand())
{
    comm.CommandText = "SELECT * FROM ScheduleDetail WHERE ScheduleID = @ScheduleID";
    comm.Parameters.Add("@ScheduleID", SqlDbType.Int).Value = lstShifts.SelectedValue;
    comm.CommandType = CommandType.Text;
    comm.Connection = conn;

    conn.Open();

    using(SqlDataReader reader = comm.ExecuteReader())
    {
        while (reader.Read())
        {
             TimeSpan DBStartTime = (TimeSpan)reader["StartTime"];
             TimeSpan DBEndTime = (TimeSpan)reader["EndTime"];
            // Add more coding once this works.
        }
    }
}

Answer 2

I think you have miss understood what a DateTime and TimeSpan are a DateTime is a certain time of the day ie 8PM on the 4th July where as a TimeSpan is a duration ie 9hours

because if this if you do "8PM on the 4th July" - "3PM on the 4th July" you will get a TimeSpan of 5Hours, therefore if you convert directly from the database Time of 7PM to a dateTime you will get "7PM on 1st january 0001" which is completely useless. instead go straight to timespan though in this case you are slightly handicapped by the fact by the fact TimeSpan isn't a default Convertion type in Convert so you need to use the old style convertion using brackets

if(reader["EndTime"] is TimeSpan)
    DBEndTime = (TimeSpan)reader["EndTime"];