DateTime2 to C# DateTime with SqlDataReader

Question

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!

Did you try Convert.ToDateTime(reader["ConcertStart"]) instead of casting ? — Ofiris
– Ofiris, Commented Feb 28, 2015 at 10:43
it wouldn't make much difference there by doing a cast or a convert. you have not specified exactly what the problem is? It should work as the DateTime and DateTime2 data type of SQL map to Datetime in .NET — Ahmed ilyas
– Ahmed ilyas, Commented Feb 28, 2015 at 10:44
When you say that you can't seem to convert it properly, how did you determine that? What is the result that you expect, and what result do you get? — Guffa
– Guffa, Commented Feb 28, 2015 at 10:47

marc_s · Accepted Answer · 2015-02-28 10:49:07Z

8

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?

answered Feb 28, 2015 at 10:49

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

4 Comments

Dennie Over a year ago

My god, I'm such an idiot. Adding the dates to the query was all it took. You're a lifesaver.

marc_s Over a year ago

@Daemun: sometimes all it takes is just another pair of eyes :-)

jd0x0021 Over a year ago

how do we get only the date for this? like remove the 12:00:00 appended at the end?

marc_s Over a year ago

In .NET - use the .Date property on the DateTime datatype; in T-SQL - use CAST(column AS DATE)

StuartLC · Accepted Answer · 2015-02-28 10:51:10Z

1

The problem is that in your select, you only return 3 of the columns needed:

 String selectAll = "SELECT ConcertId, ConcertName, ConcertLocationId ...";

Whereas in your reader, you attempt to scrape 5 columns:

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"];

Hence the IndexOutOfRangeException. Either select all columns, or remove the extraneous ones from the reader.

The issue isn't related to Sql DateTime2 vs .Net DateTime - ADO will bind these just fine.

answered Feb 28, 2015 at 10:51

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Comments

stefankmitph · Accepted Answer · 2015-02-28 10:49:04Z

0

I'd do it with an extension method I found here

public static DateTime ToDate(this string input, bool throwExceptionIfFailed = false)
{
    DateTime result;
    var valid = DateTime.TryParse(input, out result);
    if (!valid)
        if (throwExceptionIfFailed)
            throw new FormatException(string.Format("'{0}' cannot be converted as DateTime", input));
   return result;
}

Convert your data like this:

concert.Start = reader["ConcertStart"].ToString().ToDate();
concert.End = reader["ConcertEnd"].ToString().ToDate(true); //throws an exception if it fails

Hope this helps!

answered Feb 28, 2015 at 10:49

stefankmitph

3,3062 gold badges20 silver badges23 bronze badges

Comments

Vishal · Accepted Answer · 2018-12-28 09:50:44Z

0

Anyone who wants to restore SQL DateTime2 to DateTime correctly including 'Kind=Utc'

(DateTime)reader["ConcertEnd"].ToUniversalTime();

otherwise DateTime may struggle to workout what timezone the date is from

answered Dec 28, 2018 at 9:50

Vishal

6158 silver badges12 bronze badges

Collectives™ on Stack Overflow

DateTime2 to C# DateTime with SqlDataReader

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related