Cannot implicitly convert DateTime to Timespan

Question

Sql Server has the variable TEST_TIME data type as Time(7)
I created the tables in C# and it automatically assigned the Timespan datatype.
Now, i'm trying to upload csv file data to the SQL database and it is giving me an error " Cannot implicitly convert DateTime to Timespan". What would be the best way to fix this? The user first selects the CSV file:

    private void button8_Click(object sender, EventArgs e)  
        {  
               try  
                    {  
                        using (OpenFileDialog openfiledialog1 = new OpenFileDialog()   
                        {Filter = "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx|Excel Workbook|*.xlsm|Excel Workbook|*.csv|Excel Workbook|*.txt", ValidateNames = true })                      
                        {   
--After some IFs--  

    else if (openfiledialog1.FilterIndex == 4)
                                {

                                    DataTable oDataTable = null;
                                    int RowCount = 0;
                                    string[] ColumnNames = null;
                                    string[] oStreamDataValues = null;
                                    //using while loop read the stream data till end
                                    while (!oStreamReader.EndOfStream)
                                    {
                                        String oStreamRowData = oStreamReader.ReadLine().Trim();
                                        if (oStreamRowData.Length > 0)
                                        {
                                            oStreamDataValues = oStreamRowData.Split(',');
                                            //Bcoz the first row contains column names, we will populate 
                                            //the column name by
                                            //reading the first row and RowCount-0 will be true only once
                                            if (RowCount == 0)
                                            {
                                                RowCount = 1;
                                                ColumnNames = oStreamRowData.Split(',');
                                                oDataTable = new DataTable();

                                                //using foreach looping through all the column names
                                                foreach (string csvcolumn in ColumnNames)
                                                {
                                                    DataColumn oDataColumn = new DataColumn(csvcolumn.ToUpper(), typeof(string));

                                                    //setting the default value of empty.string to newly created column
                                                    oDataColumn.DefaultValue = string.Empty;

                                                    //adding the newly created column to the table
                                                    oDataTable.Columns.Add(oDataColumn);
                                                }
                                            }
                                            else
                                            {
                                                //creates a new DataRow with the same schema as of the oDataTable            
                                                DataRow oDataRow = oDataTable.NewRow();

                                                //using foreach looping through all the column names
                                                for (int i = 0; i < ColumnNames.Length; i++)
                                                {
                                                    oDataRow[ColumnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].ToString();
                                                }

                                                //adding the newly created row with data to the oDataTable       
                                                oDataTable.Rows.Add(oDataRow);
                                            }
                                        }
                                    }
                                    result.Tables.Add(oDataTable);
                                    //close the oStreamReader object
                                    oStreamReader.Close();
                                    //release all the resources used by the oStreamReader object
                                    oStreamReader.Dispose();
                                    dataGridView5.DataSource = result.Tables[oDataTable.TableName];
                                }

Here is the Code:

    private void button9_Click(object sender, EventArgs e)
            {

                try
                {               


                    DataClasses1DataContext conn = new DataClasses1DataContext();
        else if (textBox3.Text.Contains("GEN_EX"))
            {
                foreach (DataTable dt in result.Tables)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        GEN_EX addtable = new GEN_EX()
                        {

                            EX_ID = Convert.ToByte(dr[0]),
                            DOC_ID = Convert.ToByte(dr[1]),
                            PATIENT_NO = Convert.ToByte(dr[2]),
                            TEST_DATE = Convert.ToDateTime(dr[3]),                    
                            **TEST_TIME = Convert.ToDateTime((dr[4])),**

                        };
                        conn.GEN_EXs.InsertOnSubmit(addtable);
                    }
                }
                conn.SubmitChanges();
                MessageBox.Show("File uploaded successfully");
            }  
     else
        {
            MessageBox.Show("I guess table is not coded yet");
        }
}

EDIT

The TEST_TIME represents HH:MM:SS
The Typed Data Set is defined as:

public virtual int Update(
                    byte EX_ID, 
                    byte DOC_ID, 
                    byte PATIENT_NO, 
                    System.DateTime TEST_DATE, 
                    System.TimeSpan TEST_TIME)

Do a `dr[4].GetType()` and see what is the type contained in the `DataRow` — xanatos, Mar 06 '17 at 14:06
what data are you trying to put in that column? (i.e. what value is in the csv that it doesn't like) — Dave Becker, Mar 06 '17 at 14:09
Is type of property `TEST_TIME` TimeSpan? `Convert.ToDateTime` will return `DateTime` and you can not directly convert DateTime to TimeSpan. DateTime represents value of specific time and date while TimeSpance represents duration in terms of hours, minutes,seconds and milliseconds. — Chetan, Mar 06 '17 at 14:11
If `TEST_TIME` is a `TimeSpan` then why are you trying to put a `DateTime` into it? — Chris, Mar 06 '17 at 14:11
The TEST_TIME variable type is TimeSpan What should i put instead of Convert.ToDateTime to get time values? @Chris Convert.ToTimeSpan is not allowed — SQLserving, Mar 06 '17 at 14:15
And what is the type of data coming in `dr[4]`? And what does it represent Number of Days, Hours, Minutes, Seconds ? — Chetan, Mar 06 '17 at 14:17
The best way to fix this would be to provide us with some details about your situation. Your question is incredibly vague. We all understand the problem you are facing but you haven't provided us with enough information so we can help. This would be a good place to start. http://stackoverflow.com/help/mcve — Sean Lange, Mar 06 '17 at 14:20
@SQLserving: Googling has turned up at least three stack overflow questions on converting a string to a TimeSpan. http://stackoverflow.com/questions/24369059/how-to-convert-string-0735-hhmm-to-timespan is one such example. — Chris, Mar 06 '17 at 14:24

Chetan · Accepted Answer · 2017-03-06T14:56:57.720

Based on your input that dr[4] represents time values in hours:minutes:seconds format I recommend following solution.

private TimeSpan GetTimeSpan(string timeString)
{
    var timeValues = timeString.Split(new char[] { ':' });
    //Assuming that timeValues array will have 3 elements.
    var timeSpan = new TimeSpan(Convert.ToInt32(timeValues[0]), Convert.ToInt32(timeValues[1]), Convert.ToInt32(timeValues[2]));
    return timeSpan;
}

Use above method as following.

else if (textBox3.Text.Contains("GEN_EX"))
{
    foreach (DataTable dt in result.Tables)
    {
        foreach (DataRow dr in dt.Rows)
        {
            GEN_EX addtable = new GEN_EX()
            {

                EX_ID = Convert.ToByte(dr[0]),
                DOC_ID = Convert.ToByte(dr[1]),
                PATIENT_NO = Convert.ToByte(dr[2]),
                TEST_DATE = Convert.ToDateTime(dr[3]),                    
                **TEST_TIME = GetTimeSpan(dr[4].ToString()),**

            };
            conn.GEN_EXs.InsertOnSubmit(addtable);
        }
    }
    conn.SubmitChanges();
    MessageBox.Show("File uploaded successfully");
}

This should give your the value you want. You will face runtime issues if value of dr[4] is not in hours:minutes:seconds format. That I will leave it up to you.

Error 1 'System.Data.DataRow' does not contain a definition for 'GetString' and no extension method 'GetString' accepting a first argument of type 'System.Data.DataRow' could be found (are you missing a using directive or an assembly reference?) I'm getting a compile error for this — SQLserving, Mar 06 '17 at 14:53
Oh... I realized it now... I have changed the answer.... Now there should not be any error... — Chetan, Mar 06 '17 at 14:57

Lostblue · Answer 2 · 2017-03-06T14:42:22.730

First of all Timespan and DateTime are 2 differents type without implicit conversion available. Since Timespan is a time value between two DateTime, you need to know your referenced time (DateTime) used to start the mesure of your Timespan.

For example it could be from DateTime dtReferential = new DateTime(1900, 01, 01);

In order to give a SQL Timespan value you need to give it a C# Timespan! Change TEST_TIME value to a Timespan. And finally, give it the substracted value of your referenced time.

Using the previous example:

else if (textBox3.Text.Contains("GEN_EX"))
{
    foreach (DataTable dt in result.Tables)
    {
        foreach (DataRow dr in dt.Rows)
        {
            GEN_EX addtable = new GEN_EX()
            {

                EX_ID = Convert.ToByte(dr[0]),
                DOC_ID = Convert.ToByte(dr[1]),
                PATIENT_NO = Convert.ToByte(dr[2]),
                TEST_DATE = Convert.ToTimespan(dr[3]),                    
                TEST_TIME = dtReferential.Subtract(Convert.ToDateTime(dr[4]))

            };
            conn.GEN_EXs.InsertOnSubmit(addtable);
        }
    }
    conn.SubmitChanges();
    MessageBox.Show("File uploaded successfully");
}

Cannot implicitly convert DateTime to Timespan

2 Answers2