String was not recognized as a valid DateTime from dataset

Question

I have a dataset from SQL server which bound the gridview on aspx page. I post the date on webpage by using the below code:

<asp:Label ID="Label10" runat="server" Text='<%# Convert.ToDateTime(Eval("date1")).ToString("yyyy/MM/dd")  %>' 

The datetime is on SQL sever is 2015-12-06 00:00:00.000 that showing as 2015/06/12 on webpage. The correct date should be 2015/12/06 (Dec 6 2015). I have globalization on webconfig.

<globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="en-US" uiCulture="en-US"/>

Can anyone tell me how to solve this problem?

Answer 1

SQL Server should have the date persisted in one of the following types and never as anything else (by anything else I am referring to varchar, text, int, BigInt, or anything else "creative")

• DateTime2
• DateTime
• Date
• DateTimeOffset

For more types please refer to Date and Time Data Types and Functions (Transact-SQL)

The display in SQL Server, therefore, should never matter because there is no actual display or formatting associated with the type. What you happen to see in the query window in SSMS does have formatting but only because it has to be displayed some how, this formatting is usually done in ISO8601 notation and has nothing to do with how the instance is actually persisted.

The returned instance in your .NET code from Sql Server should be of type System.DateTime or System.DateTimeOffset, the latter if you are also using DateTimeOffset in Sql Server which persists the offset from UTC with the instance. You can then use ToString() with various formatting options to display the DateTime as you see fit. How a DateTime is displayed / formatted should always be a presentation layer function and never anything deeper than that in the program stack.

See Custom Date and Time Format Strings for the various format string options available for .net DateTime instances

---

Coming back to the relevant code in the OP

<asp:Label Text='<%# Convert.ToDateTime(Eval("date1")).ToString("yyyy/MM/dd") %>' 

Convert.ToDateTime should be removed as the variable date1 should already be a DateTime instance (if you are following best practices as outlined above). You can then call ToString on that instance directly with the desired format string.