Insert date in dd-MM-yyyy format

Question

I'm trying to insert date in dd-MM-yyyy format in c#. Query for inserting is

SqlCommand cmd_cust = new SqlCommand(@"insert into custdetail values ('" + txtInvoiceNo.Text + "','" + txtCustomerName.Text + "','" + txt_contact.Text + "', '" + txtAddress.Text + "', '" + txt_total_amt.Text + "', '" + dt_date.Value.ToString("dd-MM-yyyy") + "')", con_create);
            con_create.Open();
            cmd_cust.ExecuteNonQuery();
            con_create.Close();

I have created table with column name date has datatype date. After inserting record the value in date column field is in yyyy-dd-MM format. I want this in dd-MM-yyyy format.

This is a matter of SQL server, not C#, I guess. Database will also append time to it. — Ondrej Janacek, Feb 01 '14 at 13:49
@ user1956570 I don't want to system date format... I want custom format... — Prasad Bhujbal, Feb 01 '14 at 13:51
Don't tell me that you store a datetime value in a string column — Steve, Feb 01 '14 at 13:55
OK just assumed that because you have single quotes around the `dt_date`, now that's just a matter to use a parameter with datetime type as explained in my answer — Steve, Feb 01 '14 at 14:00

score 8 · Accepted Answer · edited May 23 '17 at 12:21

Do not try to concatenate a string to build a correct sql command.
This leads only to parsing problems and Sql Injection Attacks.
Use instead a parameterized query

int isok = 0;
try 
{
    // Now your query is more readable and there are no more formatting problems here
    SqlCommand cmd_cust = new SqlCommand(@"insert into custdetail values
                         (@invNo,@custName,@contact,@address,@amount,@dt)", 
                         con_create);
    con_create.Open();
    cmd_cust.Parameters.AddWithValue("@invNo",txtInvoiceNo.Text );
    cmd_cust.Parameters.AddWithValue("@custName",txtCustomerName.Text );
    cmd_cust.Parameters.AddWithValue("@contact",txt_contact.Text);
    cmd_cust.Parameters.AddWithValue("@address",txtAddress.Text.Text);
    // The following parameter could require a conversion if the db field is not of text type
    // cmd_cust.Parameters.AddWithValue("@amount", Convert.ToDecimal(txt_total_amt.Text)); 
    cmd_cust.Parameters.AddWithValue("@amount", txt_total_amt.Text); 
    cmd_cust.Parameters.AddWithValue("@dt",dt_date.Value );
    isok= cmd_cust.ExecuteNonQuery();
    con_create.Close();
}

Using a parameter you don't need to worry how to format a DateTime value to a string, you pass directly the DateTime value as expected by the database field. It is the framework job to correctly pass this value to the underlying database table.

This is true also for the other fields like the string ones. If your user types a single quote inside one of your textboxes you get a syntax error with the string concatenation. The quote typed by your user mistakenly closes the value leaving the remainder of the text as invalid sql text
(e.g. textCustomerName.Text = O'Brian becomes ....,'O'Brian' ,....)

theMayer · Answer 2 · 2014-02-01T14:29:00.237

0

I agree with Steve's answer above. But, to focus on your specific question, SQL does not store the date in a specific format - it stores it as two integers (in binary). Therefore, the date you see in the query window (or wherever else you are looking at it) does not matter; likewise, whatever format you attempt to insert into the database does not matter (as long as SQL server can parse it correctly). If you want the output to look a certain way, you can re-format to suit your needs on a SELECT query. Both SQL and C# have extensive date formatting methods.

Are you formatting the date in a SQL query output or a C# program output?

edited Feb 01 '14 at 14:29

answered Feb 01 '14 at 14:22

theMayer

15,456
7
58
90

I used dt_date.Value.ToString("dd-MM-yyyy") this to change format – Prasad Bhujbal Feb 01 '14 at 14:24
That is not true. You are trying to change format on the input side of the database, which is not relevant. If you want format to change, it has to be done on the output side. – theMayer Feb 01 '14 at 14:27
How to change it on output side.? – Prasad Bhujbal Feb 01 '14 at 14:34
There are lots of ways. It depends on how/where your end user will look at it. Please describe that further. – theMayer Feb 01 '14 at 14:35
Actually I want to display records between selected dates as @"select invoiceid,custname,contact,date,totalfees from custdetail where date between '" + dt_from.Value.ToString("dd-MM-yyyy") + "' and '" + dt_to.Value.ToString("dd-MM-yyyy") + "'" – Prasad Bhujbal Feb 01 '14 at 14:41
OK, that is a different question than you asked. And date display format is still irrelevant to the SQL engine. – theMayer Feb 01 '14 at 14:46

Insert date in dd-MM-yyyy format

2 Answers2

Linked