My SQL Server is using the DATE datatype, but my C# app is reading this as 'mm/dd/yyyy 00:00:00', can anyone tell me how to get rid of the 0s? (everything else works)...? Answer If you don't want the time portion of a DateTime as String you have several options: Read: Date and time format strings

C# is adding 00:00:00 unnecessarily

My SQL Server is using the DATE datatype, but my C# app is reading this as ‘mm/dd/yyyy 00:00:00’, can anyone tell me how to get rid of the 0s? (everything else works)…?

var sda = new SqlDataAdapter("SELECT CustomerOrders.OrderID, Product.ProductName, OrderDetails.QuantityOrdered," +
" Customer.CustomerFirstName, Customer.CustomerLastName, CustomerOrders.OrderDate FROM CustomerOrders" +
" INNER JOIN Product ON CustomerOrders.ProductID = Product.ProductID INNER JOIN Customer on CustomerOrders.CustomerID = Customer.CustomerID" +
" INNER JOIN OrderDetails ON CustomerOrders.OrderID = OrderDetails.OrderID;", DatabaseConnection); // uses SQL query to read every piece of data in database
var dataset = new DataSet();
sda.Fill(dataset);
DatabaseConnection.Close();

DatabaseList.Items.Clear();                                           // clears all data in the list
DatabaseList.BeginUpdate();
foreach (DataRow row in dataset.Tables[0].Rows)
{
    var lvi = new ListViewItem();                                     // rebuilds the list
    lvi.Text = row["OrderID"].ToString();
    lvi.SubItems.Add(row["ProductName"].ToString());
    lvi.SubItems.Add(row["QuantityOrdered"].ToString());
    lvi.SubItems.Add(row["CustomerFirstName"].ToString());
    lvi.SubItems.Add(row["CustomerLastName"].ToString());
    lvi.SubItems.Add(row["OrderDate"].ToString());
    DatabaseList.Items.Add(lvi);
}
DatabaseList.EndUpdate();

Answer

If you don’t want the time portion of a DateTime as String you have several options:

DateTime orderDate = row.Field<DateTime>("OrderDate");

lvi.SubItems.Add(orderDate.ToShortDateString());

lvi.SubItems.Add(orderDate.ToString("d"));

lvi.SubItems.Add(orderDate.ToString("dd/MM/yyyy");

Read: Date and time format strings

Advertisement

Answer