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 :)