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

qqrboqgw 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(111)

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?

sql-server

来源：https://stackoverflow.com/questions/39013132/how-to-make-sqldatareader-to-get-time7-type-variable

3条答案

按热度按时间

djmepvbi1#

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();
}

赞(0）回复(0）举报 2023-10-15

zy1mlcev2#

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.

赞(0）回复(0）举报 2023-10-15

mitkmikd3#

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)

赞(0）回复(0）举报 2023-10-15

我来回答

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

3条答案

相关问题

热门标签

最新问答