DateTime2 to C# DateTime with SqlDataReader

c#, datetime, datetime2, sql-server, sqldatareader

Solution

First of all, if you want to read the value of `ConcertStart` and `ConcertEnd`, you'd have to include them in your `SELECT`!!$

string selectAll = @"SELECT ConcertId, ConcertName, ConcertLocationId,
                            ConcertStart, ConcertEnd     <<--- add these!! 
                     FROM dbo.Concerts";

Try this:

while (reader.Read())
{
    Concert concert = new Concert();

    concert.Id = (int)reader["ConcertId"];
    concert.Name = reader["ConcertName"].ToString();
    concert.LocationId = (int)reader["ConcertLocationId"];
    concert.Start = reader.GetFieldValue<DateTime>(reader.GetOrdinal("ConcertStart"));
    concert.End = reader.GetFieldValue<DateTime>(reader.GetOrdinal("ConcertEnd"));

    concerts.Add(concert);
}

I have no trouble at all reading out a `DATETIME2(3)` value from the SQL Server database using

reader.GetFieldValue<DateTime>(reader.GetOrdinal("ConcertEnd"));

Does that work for you?

Problem

I realize this might be a dupe, but I've spent hours searching for the answer and can't seem to find it. I'm currently creating a web API that retrieves concert data. I have a SQL Server table that holds a start and end date, both as a `datetime2` type. I've inserted the dates in this format and they don't cause any problems when viewing the database: ``` 2015-10-08T20:00:00.0000000+01:00 ``` My model: ``` public class Concert { public int Id { get; set; } public string Name { get; set; } public int LocationId { get; set; } [Column(TypeName = "DateTime2")] public DateTime Start { get; set; } [Column(TypeName = "DateTime2")] public DateTime End { get; set; } public string Description { get; set; } public string Url { get; set; } } ``` And my method in the class that brings up my database data: ``` public List<Concert> getAll() { List<Concert> concerts = new List<Concert>(); SqlConnection connection = CasWebAPIdb.getConnection(); String selectAll = "SELECT ConcertId, ConcertName, ConcertLocationId FROM dbo.Concerts"; SqlCommand selectCommand = new SqlCommand(selectAll, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); var isoDateTimeFormat = CultureInfo.InvariantCulture.DateTimeFormat; while (reader.Read()) { //Debug.WriteLine("lol: " + reader["ConcertEnd"].GetType()); Concert concert = new Concert(); concert.Id = (int)reader["ConcertId"]; concert.Name = reader["ConcertName"].ToString(); concert.LocationId = (int)reader["ConcertLocationId"]; concert.Start = (DateTime)reader["ConcertStart"]; concert.End = (DateTime)reader["ConcertEnd"]; concerts.Add(concert); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return concerts; } } ``` I get this error when debugging: An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code I have tried a lot of things and followed a lot of examples and codes, but I can't seem to convert this properly. Does anybody have an idea? solution I forgot to add the 'concertStart' and 'concertEnd' to my query. Problem solved, thanks!

Original source