DateTime Variable Not Grabbing the Milliseconds from SQL Server table

Question

_{Friday, October 30, 2015 10:43 PM}

Hello all. I have a value in my SQL Server table of 2015-10-30 12:17:41.007.

This line of C# code runs:

DateTime TrialStart = DateTime.Parse(TrialAbstractDT.Rows[0]["TrialStartTime"].ToString());

And the value of TrialStart is 10/30/15 12:17:41. When I examine the variable further it says milliseconds is 0. The field type in SQL Server is DateTime2(3). I have also tried just using DateTime in SQL Server. What am I missing?

Edit:

I tried changing the above line to:

string TrialStart = DateTime.Parse(TrialAbstractDT.Rows[0]["TrialStartTime"].ToString()).ToString("hh:mm:ss.fff");

and the value of TrialStart is 10/30/15 12:17:41.000

I know for a fact that in the database, the field is 2015-10-30 12:17:41.007

Hmmm. What am I doing wrong?

All replies (9)

_{Monday, November 2, 2015 6:50 AM ✅Answered}

Hi David,

Per my understanding, That is because the default format string for DateTime doesn't include milliseconds.

You may need to custom format, you will see the milliseconds value.

Here I also found a sample from the following thread which replied by Magnus

No milliseconds value when reading DateTime values from a SQL database in C#

Best regards,

Kristin

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

_{Monday, November 2, 2015 3:59 PM ✅Answered}

DateTime.ToString() will drop the milliseconds.  If your DB column is a DATETIME then the returned column value is already a DateTime. You don't want to convert to string.  Instead use Convert.ToDateTime to take the column value directly.

var dt = Convert.ToDateTime(TrialAbstractDT.Rows[0]["TrialStartTime"]);

Michael Taylor
http://blogs.msmvps.com/p3net

_{Friday, October 30, 2015 11:19 PM}

I think you are doing right, could be your DAL.

Check the "TrialAbstractDT.Rows[0]["TrialStartTime"]" value during debug (Add to watch).

_{Monday, November 2, 2015 4:36 PM}

Thank you Kristin and Michael. I was able to get it to work with your example Kristin. Strangely enough Kristin, I had run across that post before, but I could not figure out how to implement it. But I do have it working now.

David

 SQL = "SELECT * FROM Config.TrialAbstract WHERE TrialID = '" + m_trialID + "' ";

            using (SqlCommand metadataCmd = new SqlCommand(SQL, m_dbSettings.sqlMetadataDbConn))
            {
                using (SqlDataAdapter metadataAdap = new SqlDataAdapter(metadataCmd))
                {
                    TrialAbstractDT = new DataTable();
                    metadataAdap.Fill(TrialAbstractDT);
                }

                SqlDataReader reader = metadataCmd.ExecuteReader();


            

            //Define columns of DataGrid DataTable - Similar to a Create Table statement
            DataGridDT = new DataTable();
            DataGridDT.PrimaryKey = new DataColumn[] { DataGridDT.Columns["ID"] };
            DataGridDT.Columns.Add("TrialID", typeof(String));
            DataGridDT.Columns.Add("TrialDuration", typeof(String));
            DataGridDT.Columns.Add("ObserverNotesProcessed", typeof(bool));
            DataGridDT.Columns.Add("OperatorDebriefsProcessed", typeof(bool));
            DataGridDT.Columns.Add("SurveysProcessed", typeof(bool));
            
            dataGridView1.Height = 100; //Make Shorter - Original height was 796

            DataGridRow = DataGridDT.NewRow();
            string TrialID = TrialDataDT.Rows[0]["TrialID"].ToString();
            
            while (reader.Read())
            {
                TrialStart = (DateTime)reader["TrialStartTime"];
                TrialEnd = (DateTime)reader["TrialStopTime"];
                int milliSeconds = TrialStart.Millisecond;
                Console.WriteLine(TrialStart.ToString("yyyy-MM-dd HH:mm:ss.fff"));
                TimeSpan span = TrialEnd.Subtract(TrialStart);
                string Duration = span.ToString();
            }
       }

_{Monday, November 2, 2015 4:44 PM}

Michael, this is actually a great solution as well. Thank you. With this solution, I do not have to create and implement a reader.

_{Monday, November 2, 2015 11:09 PM}

Never embed parameters directly into the code. Always use parameters if you don't want to become a subject of SQL injection attack.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Tuesday, November 3, 2015 6:03 PM}

Hi Naomi. I am sorry, but I am not following you. Can you please explain how I am using parameters incorrectly?

Thanks,

David

_{Tuesday, November 3, 2015 7:15 PM | 1 vote}

Here

SQL = "SELECT * FROM Config.TrialAbstract WHERE TrialID = '" + m_trialID + "' ";

It should be instead:

SQL = "SELECT * FROM Config.TrialAbstract WHERE TrialID = @TrialId";

and then use metadataCmd.Parameters.Add and add that parameter.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

_{Tuesday, November 3, 2015 8:26 PM}

Ok, thank you. I will do some research on that.