How do I read a datetime2(7) field as defined in SQL?

Question

0.00/5 (No votes)

See more:

Stored in a SQL table is a date/time field and it is defined as:

CreationDateTime datetime2(7) allow nulls and has stored in it 2023-06-06 09:28:37.6271104.

I have never processed a datetime beyond the standard MM/dd/yyyy hh:mm:ss. When I read the field from the databse table:

creationDateTime = Reader["CreationDateTime"].ToString();

all I get is 2023-06-06 09:28:37 and not the 2023-06-06 09:28:37.6271104.

What I have tried:

I have tried everything but nothing works. What am I doing wrong? Thanks in advance for the help?

Posted 6-Jun-23 9:52am

Member 13694735

Updated 6-Jun-23 10:22am

Add a Solution

Comments

PIEBALDconsult 6-Jun-23 19:29pm

Dunno. I think something is truncating it along the way. Could be the query or something.

Richard Deeming 7-Jun-23 4:46am

Try reading the value as a DateTime, not a string:

int index = Reader.GetOrdinal("CreationDateTime");
DateTime? creationDateTime = Reader.IsDBNull(index) ? null : Reader.GetDateTime(index);

If you still get a truncated value, then you need to check the query to see what's truncating it.

Member 13694735 7-Jun-23 14:36pm

this did the trick
var d = (DateTime)Reader["CreationDateTime"];
var dd = d.ToString("MM/dd/yyyy hh:mm:ss.fffffff tt");

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-06-06T10:22:00

Solution 1

You are using the default ToString because you don't provide any specific formatting info.
Provide the right format, and you should get what you want: Formatting a DateTime for display - format string description[^]

Posted 6-Jun-23 10:22am

OriginalGriff

Comments

Member 13694735 6-Jun-23 16:37pm

I do this using "yyyy-MM-dd hh:mm:ss.fffffff" and this what I get 2023-06-06 09:28:37.0000000.

OriginalGriff 7-Jun-23 0:40am

Without your actual code fragment, we can't help much further: certainly the data returned is fine for me:

            string strConnect = SMDBSupport.SMInstanceStorage.GetInstanceConnectionString("Testing");
            using (SqlConnection con = new SqlConnection(strConnect))
                {
                try
                    {
                    con.Open();
                    using (SqlDataAdapter da = new SqlDataAdapter("Select * from Table_3", con))
                        {
                        using (DataTable dt = new DataTable())
                            {
                            da.Fill(dt);
                            myDataGridView.DataSource = dt;
                            myDataGridView.ClearSelection();
                            myDataGridView.Columns[1].DefaultCellStyle.Format = "yyyy-MM-dd hh:mm:ss.fffffff";
                            }
                        }
                    }
                catch (Exception ex)
                    {
                    Debug.WriteLine(ex.ToString());
                    }
                }

Member 13694735 7-Jun-23 14:37pm

var d = (DateTime)Reader["CreationDateTime"];
var dd = d.ToString("MM/dd/yyyy hh:mm:ss.fffffff tt");

THIS WORKED.