C# changing date format from SQL Server

Question

I'm relatively new to C#, SQL Server, and Stackoverflow.

I want the format of my date to stay the same from SQL Server to my C# code. I have a screenshot of the SQL Server table:

a screenshot of the sql table .

I just want the format to stay the same. I want the format to stay yyyy-mm-dd instead when I retrieve the data the format is dd/mm/yyyy 0:00:00.

I then use the following code to retrieve the data from the server:

ArrayList a = new ArrayList(); 
DataTable d = new DataTable();         
string sql = "SELECT * FROM myServer";

MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn);
MySqlDataAdapter myA = new MySqlDataAdapter(cmd);

myA.Fill(d);
cmd.ExecuteNonQuery();

foreach (DataRow row in d.Rows)
{
    FitnessManager f = new FitnessManager();
    f.testDate = row["testDate"].ToString();
    f.tDate = row["tDate"].ToString();
    f.dtDate = row["dtDate"].ToString(); 
    a.Add(f);
}

return a;

I would expect the results in the ArrayList to be 2020-10-13, instead I'm getting 10/11/2020 0:00:00.

I do not know why this happens for how to fix this.

Both a DATE in SQL and a DateTime in .NET are binary objects. What you see in both cases is two different representations of the same (or maybe translated, but similar) binary data. In both systems there are many options to control the translation from binary data to strings. — Peter B
– Peter B, Commented Oct 13, 2020 at 21:29
Your posted code snippet is marked JavaScript, not C#. The screenshot of the table is not useful - please specify which SQL datatype your datetime is in SQL (there are several, such as datetime, smalldatetime, etc). Please improve this post so we can help you. — CoolBots
– CoolBots, Commented Oct 13, 2020 at 21:30
Why do you use cmd.ExecuteNonQuery();? It useless in your code. — Alexander Petrov
– Alexander Petrov, Commented Oct 13, 2020 at 21:32
So the datatypes in the sql table are DATE datatypes. I was using ArrayList, because it is what I'm using in my larger project. cmd.ExecuteNonQuery() was just a copy and paste from another part of my code. — Peteball
– Peteball, Commented Oct 13, 2020 at 21:49

E.J. Brennan · Accepted Answer · 2020-10-13 21:31:25Z

2

You would do something like this:

  f.tDate = row["tDate"].ToString("yyyy-MM-dd")

The ToString() function accepts a format string, so you can pretty much convert it to whatever format you want.

More information here:

https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

answered Oct 13, 2020 at 21:31

E.J. Brennan

46.9k8 gold badges94 silver badges120 bronze badges

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

Comments

Peteball · Accepted Answer · 2020-10-13 21:58:54Z

0

I found a previous post that helped me out a lot.

Change date format in C# when reading from database and setting to label I needed to specify that the data being formatted was datatime rather than ToString() being called as an object.

Thanks E.J for putting me on the right track.

answered Oct 13, 2020 at 21:58

Peteball

91 bronze badge

Collectives™ on Stack Overflow

C# changing date format from SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related