Search code examples
c#asp.netdatedatasetstring.format

String.Format is not working on dates


I am executing a sql query and storing it in a dataset, from there i am sending an email, everything works as expected, except for this time attached to the date 12:00:00 Am, it looks really weird on the email, i know that this occurs because the data type of that column is date and the object it gets attached to stores it as datetime and hence gets reset to midnight but i am querying sql directly and retrieving the value from the datatable but it still has the time attached to it. anyways to fix this issue i tried the below but it wont work:

string conn = ConfigurationManager.ConnectionStrings["WorkOrderConnectionString3"].ToString();
SqlConnection sqlconn = new SqlConnection(conn);
cmd.Connection = sqlconn;
sqlconn.Open();
cmd.CommandType = CommandType.Text;
String getdatasql = "SELECT WorkOrderNum, Requestor, Date, Department, CompletionDate, MachineDescription, MachineLocation," +
"[Type of Work Order], [Work Required], [WorkPerformed / PartsUsed], [Work Completed By :], [Maint. Supv. Approval]," +
" [Work Comp Date], [Supv Approval Date], Status, [Maint. Supv. Approval Date]" +
"FROM Master WHERE ([Type of Work Order] = N'General') AND (WorkOrderNum = @rockbottom) AND Status = 'Complete' ORDER BY WorkOrderNum DESC";
cmd.CommandText = getdatasql;
cmd.Parameters.AddWithValue("@rockbottom", TextBox10.Text);
SqlDataAdapter getdata = new SqlDataAdapter(cmd);
DataSet ds1 = new DataSet();
showdata.Fill(ds1);
string WorkOrderNum = ds1.Tables[0].Rows[0]["WorkOrderNum"].ToString();
string Requestor = ds1.Tables[0].Rows[0]["Requestor"].ToString();
string Date = ds1.Tables[0].Rows[0]["Date"].ToString();
String date = String.Format("{0:MM/d/yyyy}", Date);
cmd.ExecuteNonQuery();

When i did an debug i got this:

enter image description here

In my email it looks like this:

enter image description here

Tried the Solution still getting the time:

enter image description here

Got it Working :)

enter image description here


Solution

  • This is the problem:

    string Date = ds1.Tables[0].Rows[0]["Date"].ToString();
    

    Your Date variable is already a string, so trying to format that as if it's a DateTime isn't going to work. You want something like:

    DateTime Date = (DateTime) ds1.Tables[0].Rows[0]["Date"];
    

    ... although I'd recommend against having a local variable named Date anyway, especially with a variable called date in scope as well...