Get DateTime from SQL server and assign it to property

Question

I have the following class:

public class Invoice
{
     public int ID {get; set;}
     public DateTime creationDate {get; set;}
}

I have invoices saved in my SQL-Server DB with the following properties:

invoice
 - invoiceID [int, null] 
 - creationDate [datetime, null]

My dates contain the following format: 2018-07-31 00:00:00.000

However, when I get an invoice with the following

SqlCommand command = new SqlCommand(@"SELECT id, creationDate FROM invoice WHERE invoiceID = @invoiceID", conn);
        command.Parameters.Add("@InvoiceID", SqlDbType.Int).Value = InvoiceID;
        {
            conn.Open();
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                temp.ID = int.Parse(reader["id"].ToString());
                temp.InvoiceDate = Convert.ToDateTime(reader["creationDate"]).ToString("yyyy/MM/dd");
            }
            conn.Close();
        }

However, when i run my code, I get the error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

What am I doing wrong?

What line throws that exception? I am 90% sure it isn't coming from the above code. That error is _usually_ related to trying to convert a date to a `SqlDateTime` (which your code isn't doing). — mjwills, Jul 25 '18 at 13:31
And what is the value in the database field for the invoice you are trying to read? — Steve, Jul 25 '18 at 13:32
You have a misunderstanding about how SQL datetime values work. Datetimes in SQL have a binary format that is not human readable. You only see yyyy-MM-dd HH:mm:ss.fff because the query tool translates it for you. ADO.Net will do the same. You already have a DateTime value. No need to convert and especially don't call ToString() — Joel Coehoorn, Jul 25 '18 at 13:33
Please update your post with a screenshot of the exception occurring, and the code behind it showing the current line being executed. Sorry to be a pain, but that exception does some unlikely to be coming from that line so I am keen to see if I am missing something. — mjwills, Jul 25 '18 at 13:36
I don't see where you're setting the `InvoiceID` variable to something. Also, why are you defaulting the datetime field of your record to null? You certainly wouldn't create an invoice record with a null datetime field. — addohm, Jul 25 '18 at 13:48
This might help: https://stackoverflow.com/questions/468045/error-sqldatetime-overflow-must-be-between-1-1-1753-120000-am-and-12-31-999#468096 — addohm, Jul 25 '18 at 13:53
If you can't find a solution, you need to post more code. Not just snippets and interpretations. — addohm, Jul 25 '18 at 13:54
A few things, unrelated to the issue you are seeing. Since your SQL fields are nullable, (not sure why CreateDate is nullable, but...), you should make the backing class contain an "int?" and a "DateTime?". You say "My dates contain the following format: 2018-07-31 00:00:00.000". Dates don't contain "formats", they contain DateTime entities. The code you show will not compile: your backing class has ID as an int, and your createDate is a DateTime. Your code converts everything to strings and assigns them. Show us code that compiles (like @joelcoehoorn 's code below) — Flydog57, Jul 25 '18 at 14:04
That exception message is thrown when you try to WRITE a C# datetime (possibly uninitialized) to your database field and not when you try to read it back. I really fail to see how ExecuteReader could throw that exception. — Steve, Jul 25 '18 at 14:22
@mjwills , not yet as i'm not understanding why i need to 'prove' i have the error thrown in my program... — user3127554, Jul 26 '18 at 06:41
Because a number of people here, including myself, believe that error is literally not possible to occur where you say it is occurring. A screenshot will quickly prove me to be incorrect, and we can get on to solving your issue. — mjwills, Jul 26 '18 at 06:42
@mjwills , thanks for the reminder, from the moment i will have this code on hands again, i will post a screenshot — user3127554, Jul 27 '18 at 08:53

Joel Coehoorn · Answer 1 · 2018-07-25T13:57:18.847

If you have a sane database schema, the createDate column is already a DateTime value. Converting a string only shoots yourself in the foot. Same for id and int.

using (var conn = new SqlConnection("connection string here"))
using (var command = new new SqlCommand(@"SELECT id, creationDate FROM invoice WHERE invoiceID = @invoiceID", conn))
{
    command.Parameters.Add("@InvoiceID", SqlDbType.Int).Value = InvoiceID;
    conn.Open();
    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        temp.ID = (int)reader["id"];
        temp.InvoiceDate = (DateTime)reader["creationDate"];
    }
}

You do need to be a little more careful if creationDate can be NULL, but otherwise that simple cast is enough.

This doesn't make any sense. His exception is occurring with his SQL execution, not with his C# code. In fairness, we're not getting the whole picture (SQL or C#) so it's hard to help anyway. — addohm, Jul 25 '18 at 13:50

Get DateTime from SQL server and assign it to property

1 Answers1