How to make SqlDataReader to get time(7) type variable?

Question

I have a SQL Server table like this:

userID int
xCoordinate int
yCoordinate int
recordTime time(7)
itemId int

I want to get all recording times of a given user (let's say user 1 for now). I used the following code for this:

public static void something()
{
    string stmt = "select * from GazeTable where id = " + 1 + " ;";

    SqlConnection conn = GetConnection();
    SqlCommand cmd = new SqlCommand(stmt, conn);

    conn.Open();

    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetDateTime(3));
        }
    }

    conn.Close();
}

But it throws an error

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

saying, this assignment is not valid.

How can I print the time in terms of ("HH:mm:ss.ffff"), for each recordTime in my table?

If I'm not mistaken the SQL Type Time(7) does not convert to DateTime but TimeSpan in C#, so try reader.GetTimeSpan(3) instead — Bojan B
– Bojan B, Commented Aug 18, 2016 at 8:24
@BojanB oh my god it worked, everywhere i searched, kept saying, datetime is the equivalent of sql's time, thank you so much — Burak Kaan Bilgehan
– Burak Kaan Bilgehan, Commented Aug 18, 2016 at 8:26

Bojan B · Accepted Answer · 2016-08-18 08:29:23Z

2

The SQL Type Time(7) is equivalent to the C# Type TimeSpan not DateTime, to get a DateTime Value the SQL Type has to be datetime.

So just change your code to read TimeSpan instead of DateTime:

public static void something()
{
   string stmt = "select * from GazeTable where id = " + 1 + " ;";
   SqlConnection conn = GetConnection();
   SqlCommand cmd = new SqlCommand(stmt, conn);

   conn.Open();
   using (var reader = cmd.ExecuteReader())
   {
      while (reader.Read())
      {
         Console.WriteLine(reader.GetTimeSpan(3));
      }
   }
   conn.Close();
}

answered Aug 18, 2016 at 8:29

Bojan B

2,1114 gold badges20 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamid Mayeli Over a year ago

A cast may be needed (SqlDataReader)reader the GetTimeSpan is not available in System.Data.IDataReader

Freddy Ayala · Accepted Answer · 2016-08-18 08:31:27Z

0

Out of the top of my head, you might need to use something like this (haven't tested):

    public static void something()
    {
        string stmt = "select * from GazeTable where id = " + 1 + " ;";

        SqlConnection conn = GetConnection();
        SqlCommand cmd = new SqlCommand(stmt, conn);

        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}", ((SqlDataReader)reader).GetTimeSpan(3).ToString(@"dd\.hh\:mm\:ss\:ff"));
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
        }
        conn.Close();
    }

You should convert the time(7) column to a timespan, you can't convert it directly to a datetime.

answered Aug 18, 2016 at 8:31

Freddy Ayala

494 bronze badges

1 Comment

Burak Kaan Bilgehan Over a year ago

probably this will work too, but i will ensure that there is no Console.WriteLine("No rows found."); case. so thanks anyways

user22690419 · Accepted Answer · 2023-10-05 15:27:32Z

0

Note that if you are using the .NET Framework 4.8 or earlier GetTimeSpan() is not available. Instead you must use (TimeSpan)reader.GetValue(columnIndex)

answered Oct 5, 2023 at 15:27

user22690419

1

2 Comments

nalka Over a year ago

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to make SqlDataReader to get time(7) type variable?

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related