Send date data to SQL database in format YYYY-MM-DD only

Question

In my database, I have a column that has a 'date' datatype.

That means It will only take dates in YYYY-MM-DD format.

My question is,

how do I send the current date from C# to the database?

Like, if I use CURDATE() in the query on PHPMyAdmin, it'd give me today's date in the format I want. But how can I somehow use that "CURDATE()" in C# Windows Form when I send information to the database?

                string query = "INSERT INTO player (date, name, level, experience) " +
                                 "VALUES ('" + DateTime.Today + "','" +
                                         getPlayerName(Tibia.Handle, (BattleList_Start + Base + (BattleList_Step * playerIndex) + 4)) + "'," +
                                         ReadInt32(LvlAdr + Base, 4, Tibia.Handle) + "," +
                                         ReadInt32(XpAdr + Base, 4, Tibia.Handle) + ")";

Upvotimg because it is a common and important question, even if it does involve a mistaken premise — Marc Gravell, Jul 02 '13 at 18:48
not sure if this is your problem, but you might want to use a parameterized query: http://stackoverflow.com/questions/2322274/c-sharp-mysqlparameter-problem — John Boker, Jul 02 '13 at 19:07
You sample contains [SQL injection](http://en.wikipedia.org/wiki/SQL_injection). Mandatory link to Bobby Tables - http://xkcd.com/327/. — Alexei Levenkov, Jul 02 '13 at 19:11

Jon Skeet · Accepted Answer · 2013-07-02T19:11:34.840

13

Okay, in my database, I have a column that has a 'date' datatype. That means it will only take dates in YYYY-MM-DD format.

No, it doesn't. It means that the values are just dates. The value in the database is a date, it's not "a date in a particular format". It's really important to understand the difference between the stored data and a textual representation which can be used for display or input.

My question is, how do I send the current date from C# to the database?

Use parameterized SQL, and set the value of the parameter to DateTime.Today. You don't need a string representation at all. So something like:

// Work out the values beforehand
string name = getPlayerName(Tibia.Handle, 
                (BattleList_Start + Base + (BattleList_Step * playerIndex) + 4));
int level = ReadInt32(LvlAdr + Base, 4, Tibia.Handle);
int experience = ReadInt32(XpAdr + Base, 4, Tibia.Handle);

// Now do the database operations
string sql = @"INSERT INTO player (date, name, level, experience) 
               VALUES (@Date, @Name, @Level, @Experience)";
using (var conn = new MySqlConnection(...))
{
    conn.Open();
    using (var cmd = new MySqlCommand(sql, conn))
    {
        cmd.Parameters.Add("@Date", MySqlDbType.Date).Value = DateTime.Today;
        cmd.Parameters.Add("@Name", MySqlDbType.VarChar).Value = name;
        cmd.Parameters.Add("@Level", MySqlDbType.Int32).Value = level;
        cmd.Parameters.Add("@Experience", MySqlDbType.Int32).Value = experience;
        int rows = cmd.ExecuteNonQuery();
        // TODO: Validation of result (e.g. that 1 row was inserted)
    }
}

Note that that will use the system default time zone to work out what date you mean by "now" - if you want a different time zone, please give more information.

Alternatively, if you use an ORM (e.g. Entity Framework, NHibernate etc) usually this would be simplified and you would never need to specify the SQL directly - the ORM should work in terms of the parameters automatically.

edited Jul 02 '13 at 19:11

answered Jul 02 '13 at 18:42

Jon Skeet

1,421,763
867
9,128
9,194

I'm absolutely confused, what is all that mess? I'm just trying send today's date to the date column on my database. As you can see here: http://i.imgur.com/VlvjnIN.png The first date was done straight from the SQL query input using CURDATE(), the others were multiple attempts of sending the date from C# (even DateTime.Today) – Evan Jul 02 '13 at 18:53
2

@user1880591: "That mess" is code which should give you a good idea of how to use parameterized SQL. You haven't provided any code at all - you didn't even say whether you were trying to insert a new row or query existing rows, so I went with the select. But most of the code should already be what you're using - unless you're using LINQ etc, in which case you should say so. – Jon Skeet Jul 02 '13 at 18:59
@user1880591 that screenshot looks like phpmyadmin, is this a mysql database? – John Boker Jul 02 '13 at 19:03
Yes, it is PHPMyAdmin using MySQL. John, I have updated the first post with the code. – Evan Jul 02 '13 at 19:04
@user1880591: Right - you're not using parameterized queries, you're inserting the values directly into the SQL. **Don't do that.** It invites SQL injection attacks, conversion problems, and generally messy code. I'll update my answer with the MySql types. (In future, please make that sort of thing clear to start with.) – Jon Skeet Jul 02 '13 at 19:06
I see the update now. I understand there are such things as SQL Injections, but that was the least of my concerns. The program was made to be used only by me, so I never really thought about SQL injections. On top of that, I've never worked with preventing them either, so lack of experiences plays a role in that as well. From the edited post of yours, John Skeet, what is exactly preventing SQL injections? A separate operation where data is added (using methods) to an existing query string? – Evan Jul 02 '13 at 19:19
@user1880591: The fact that the data is separate from the "code" (the SQL) prevents a SQL injection attack. You're not relying on the name not containing an apostrophe, for example. There's no aspect of a parameter value which can be misinterpreted as being part of the SQL, which is where SQL injection attacks come from. – Jon Skeet Jul 02 '13 at 19:30
Alright, thanks bunch, John. I'm sorry for my little rage earlier. – Evan Jul 02 '13 at 19:32

Send date data to SQL database in format YYYY-MM-DD only

1 Answers1