My Date Format in DGV Table is Messed up any idea how to fix it?-CodePudding

So the date in my DGV table showed this format (12/31/2022 12:00:00 AM) I tried to get rid of the time to make it (12/31/2022) when I did that my date format got messed up it became (202,231,12). How do I fix it?

        cm = new MySqlCommand("SELECT OrderID, FORMAT(Orderdate, '%Y-%m-%d') as OrderDate, CustomerID, Username, ProductID, Qty, Price, TotalAmount FROM ordertb WHERE OrderDate LIKE'%"   txtSearch.Text "%'", con);

CodePudding user response：

Simply use the DATE_FORMAT() function to perform date formatting.

DATE_FORMAT(YourTableFieldName,'%m/%d/%Y')

In your case: cm = new MySqlCommand("SELECT OrderID, DATE_FORMAT(Orderdate, '%m/%d/%Y') as OrderDate, CustomerID, Username, ProductID, Qty, Price, TotalAmount FROM ordertb WHERE OrderDate LIKE'%" txtSearch.Text "%'", con);

Get more help from w3schools

CodePudding user response：

I will never get it right if you try to post a string for searching over a column of type date. Instead you should use parameters to avoid SQL Injection and to avoid parsing problems on the string passed to the database.

First you need to check if the input is indeed a datetime value. (the exact format to pass at the TryParseExact depends on your current inputs)

if (!DateTime.TryParseExact(txtSearch.Text, "MM/dd/yyyy HH.mm", CultureInfo.CurrentUICulture,
             DateTimeStyles.None, out DateTime searchedDate)
      
    ... not a date, display error, return....        

// Prepare the sql command avoiding string concatenations but
// using parameters placeholders.
cm = new MySqlCommand(@"SELECT OrderID, OrderDate, CustomerID,  
                        Username, ProductID, Qty, Price, TotalAmount  
                        FROM ordertb 
                        WHERE OrderDate = @searchDate", con);

 // Add the value for the parameter. Note that we will require 
 // a parameter of type date and just the date part of the datetime
 cm.Parameters.Add("@searchDate", MySqlDbType.Date).Value = searchedDate.Date;
 .... execute the query....

And to set the format for a specific DataGridView column use (for example the third column)

dataGrid.Columns[2].DefaultCellStyle.Format = "MM/dd/yyyy";

Finally, if your column OrderDate stores also the time, then you should use a sligthly different WHERE condition

WHERE OrderDate >= @startDate and OrderDate < @endDate", con);

cm.Parameters.Add("@startDate", MySqlDbType.Date).Value = searchedDate.Date;
cm.Parameters.Add("@endDate", MySqlDbType.Date).Value = searchedDate.Date.AddDays(1);