c# sql date return without time

Question

Hi I am trying to get the date from a sql table however whenever I get it, it returns with the time. Even though in the database table it does not have a time.

        con.Open();
        OdbcCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Select I_InvoiceNo, I_Date from tblOrdInvoice where O_OrderNo = '" + orderNoTxt.Text + "'";
        cmd.ExecuteNonQuery();
        con.Close();
        DataTable dt = new DataTable();
        OdbcDataAdapter sda = new OdbcDataAdapter(cmd);
        sda.Fill(dt);
        foreach (DataRow dr in dt.Rows)
        {
            invoiceNoTxt.Text = dr["I_InvoiceNo"].ToString();
            invoiceDateTxt.Text = dr["I_Date"].ToString();
        }

The date is being returned as "27/03/2017 00:00:00" . I only want the date. Any Help please.

It is a datetime for net. Convert to DateTime, then ToString("dd/MM/yyyy") — Steve, Mar 27 '17 at 10:31
@ZeeSoft For preventing SQL injection, used parameterized queries: http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp — ProgrammingLlama, Mar 27 '17 at 10:43
You don't need to convert anything, there's nothing wrong with the result. DateTime is a *binary* value. If you want to format it in a certain way, specify it as an argument to `ToString()`, eg `ToString("d")` or `.ToString("yyyy-MM-dd")` — Panagiotis Kanavos, Mar 27 '17 at 10:45

score 1 · Accepted Answer · answered Mar 27 '17 at 10:31

1

Change the code to:

string dateFormat = "dd-MMM-yyyy";
invoiceDateTxt.Text = dr.Field<DateTime>("I_Date").ToString(dateFormat);

Change your dateFormat variable to whatever format you like.

answered Mar 27 '17 at 10:31

Sadique

22,572
7
65
91

when i try for another function i get an error. orderDateTxt.Text = dt.Rows[0]["O_OrderDate"].ToString(); – zayn94 Mar 27 '17 at 10:37
@ZeeSoft Try this: `dt.Rows[0].Field("I_Date").ToString("dd-MMM-yyyy")` – Sadique Mar 27 '17 at 10:39

score 0 · Answer 2 · answered Mar 27 '17 at 10:43

You can convert DateTime to whichever format you want from code side. For example by using :

invoiceDateTxt.Text = dr["I_Date"].ToString("dd/MM/yyyy");

Or if you want to fetch only date part from mysql , then you can use something like below:

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) 'Date Part Only'

RESULT:
Date Part Only
--------------
20170327
Example 2:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 'Date Part Only'

RESULT:
Date Part Only
--------------
2017/03/27

For more details visit this link.

String formatting on the server side is typically a bug. The server has no idea about the client's locale. — Panagiotis Kanavos, Mar 27 '17 at 10:47

c# sql date return without time

2 Answers2