Discrepancies with the hour Format using C# and SQL Server

Question

I'm beginner with SQL and C#. I'm doing a small programm, which allows to connect with an existing database (see code below). The connection and data transfer to SQL Server is done correctly. However, the format of the hour is not the desired. The date and time that I become is shown in the following picture, but I would like to have like this "02.02.2018 14:48". What recommend to me, I have tried 10 different ways but the format remains the same. I'd appreciate your help. :)

namespace SQLConnect
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void CmdConnect_Click(object sender, EventArgs e)
        {
            SqlConnection myConnection = new SqlConnection("server=LP003;" +
                                       "Trusted_Connection=yes;" +
                                       "database=Margy; " +
                                       "connection timeout=5");
            try{
                Console.WriteLine("Connecting to MySQL...");
                myConnection.Open();
                DateTime dateTime = DateTime.Now;
                //string dateTime = DateTime.Now.ToString("F");
                string sql = "INSERT INTO tblSeeger (PersonalID, DateHour,  ProductID, LowLimit, HighLimit, Value, ModifiedValue, machineID, itemID) VALUES ('0001','"+ dateTime + "','x','1,0','1,5','1,2','0,05','0001','123456')";
                SqlCommand cmd = new SqlCommand(sql, myConnection);
                SqlDataReader rdr = cmd.ExecuteReader();

                MessageBox.Show("Well done!");
            }
            catch (SqlException ex){
                MessageBox.Show("You failed!" + ex.Message);
            }

            myConnection.Close();
            Console.WriteLine("Done. ");
        }
    }
}

When stored, dates don't have a format. They're just a number. You're concerned with display. What kind of control do you use to display the values? Research its documentation. You probably simply have to configure the UI culture. — CodeCaster, Feb 02 '18 at 14:05
you cannot control the date format in SQL server internally...its a date field...doesnt inherently have the concept of a format....the formatting is done on display of the data..not on save — Ctznkane525, Feb 02 '18 at 14:05
Use appropriate data types (and parameters) to avoid working with *strings* at all. Then these *presentation* concerns should be dealt with in the *application*, as close to final output as possible. If the database is storing it in a `datetime` or `datetime2` column, good. Leave it that way. Those data types don't *have* a format. — Damien_The_Unbeliever, Feb 02 '18 at 14:05
Is that picture a picture of a SQL table in SQL Server Management Studio? If so, you are just seeing a representation of the data (it may or may not be based on culture settings). Actual Date data is stored as a number, it just happens to be shown to you in that format (which is sorta meaningless, you can format it however you like in C# using ToString() formats) — maccettura, Feb 02 '18 at 14:05
Also, please do not concatenate strings in SQL queries, you are exposing your application to SQL injection attacks. Use paramererized queries, see [this question](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) for more details — maccettura, Feb 02 '18 at 14:06
@CodeCaster I'm undoing that "close as dup" because the question isn't about `DataGridView` - it is about SQL and databases — Marc Gravell, Feb 02 '18 at 14:07
btw, `SqlDataReader rdr = cmd.ExecuteReader();` is almost certainly wrong - that isn't a query - so `cmd.ExecuteNonQuery();` — Marc Gravell, Feb 02 '18 at 14:12
it is very unclear whether you're asking about *displaying* dates in an appropriate format, vs *storing* dates correctly. They're very different, and it is unclear which you're trying and struggling to do. What is the image? is that your app? or the database raw table view? — Marc Gravell, Feb 02 '18 at 14:15

Marc Gravell · Accepted Answer · 2018-02-02T14:18:30.227

If the problem is storing data:

Simply: don't concatenate values to make SQL strings. Ever. (sings: "well hardly everrrrr").

Seriously, though: parameters. This solves all formatting issues and prevents SQL injection:

string sql = @"
INSERT INTO tblSeeger (PersonalID, DateHour,  ProductID, LowLimit, HighLimit,
                       Value, ModifiedValue, machineID, itemID)
VALUES ('0001',@date,'x','1,0','1,5',
        '1,2','0,05','0001','123456')"; // note: these probably shouldn't be in quotes
SqlCommand cmd = new SqlCommand(sql, myConnection);
cmd.Parameters.AddWithValue("@date", dateTime);

Or alternatively - if you will always mean "now" (as per the question): you might be able to use your database's time functions - for example GETDATE() and GETUTCDATE() on SQL Server.

If adding lots of parameters is a pain (and: it is) - tools like "dapper" exist that will make it a breeze:

conn.Execute(sql, new { date = dateTime });

If the problem is displaying date, then keep in mind that the value in the database is just a number: it does not have a format. It only gets a format when you display it in the UI, so then the issue is one of configuring your UI code to use your chosen format. But emphasis: a date/time does not have a format. Most data-bound UI tools will have "format" properties on columns. If you're building the data as text manually, then you'll have to format it manually. Also check that you've set the culture correctly in your app, if the default local culture isn't the one you want to use.

Discrepancies with the hour Format using C# and SQL Server

1 Answers1