Get right datetime format from SQL reader

3.1k views Asked by At

Problem: I got SQL database with column of type datetime and I am trying to get the value to my c# API variable of type DateTime.

datetime format in SQL DB:  2011-11-14 00:00:00.000  // So that equals:
                            yyyy - MM - dd HH:mm:ss.ttt

Here are some of my attempts to get right value:

MyClass.MyProperty= Convert.ToDateTime(reader.GetSqlDateTime(0));
MyClass.MyProperty= Convert.ToDateTime(reader.GetDateTime(0));
MyClass.MyProperty= Convert.ToDateTime(reader.GetValue(0));
MyClass.MyProperty= DateTime.ParseExact(reader.GetValue(0).ToString(), "yyyy - MM - dd HH:mm:ss.ttt", CultureInfo.InvariantCulture);

Every time that my attempt compiles without error I get following output:

0001-01-01T00:00:00

I also tried in combination with DateTimeOffset and getting value from DB in string and then converting it to Datetime, but VS

Update:

I tried executing my SQL statement on database via server and found out that UserGuid of my profile didn't have any time in selected column. (I used different user credentials when I initially tested SQL statement) I fixed couple of things and debugged a bit more and now connection and reader work as they should. I still have problem with formatting tho.

DB value: 2014-08-07 00:00:00.000
Output: 8/7/2014 12:00:00 AM

I tried telling it how to format DateTime format with ParseExact, but it throws this exception:

System.FormatException' in mscorlib.dll

Additional information: String was not recognized as a valid DateTime.

Here is my code for entire reader:

        while (reader1.Read())
        {
            newUsagePlan.PlanValidityFrom = DateTime.ParseExact(Convert.ToString(reader1.GetValue(0)), "yyyy - MM - dd HH:mm:ss.ttt", CultureInfo.InvariantCulture);
            string lol1 = reader1.GetValue(0).ToString(); // Output: "8/7/2014 12:00:00 AM"
            string lol2 = reader1.GetSqlDateTime(0).ToString(); // Output: "8/7/2014 12:00:00 AM"
        }

FINAL CONCLUSION:

I solved this problem easily now, by simply using this:

string sqlFormattedDate = reader1.GetDateTime(0).ToString("yyyy-MM-dd HH:mm:ss.fff");

And got desired output: 2014-08-07 00:00:00.000

One piece of advice: Try not to work on a production and development database in a single project, it can blow your brains out when there are inconsistencies in database data and you are not aware of it. Cheers :)

0

There are 0 answers