Insert DateTime from JSON response in MySQL DB using C#

Question

I've searched this for what I feel to be a lot but all the threads appear to be looking to insert he Date Time now, which I am not..

I currently receive a Date Time in the following format:

"Date":"2018-03-03T11:00:00Z"

I'm attempting to insert into a MySQL database where the column data set is DATETIME

I'm using the following INSERT statement:

"INSERT INTO tblname (col1, col2) VALUES (@Name, @Date) ON DUPLICATE KEY UPDATE col1 = @Name";

Supplying the values via the AddWithValue parameter as follows:

Command.Parameters.AddWithValue("@Date", Jo["Results"][x]["Date"]);

This always results in me receiving the following within he database:

0000-00-00 00:00:00

A full modified version (to keep it short) of the code used is below:

using MySql.Data.MySqlClient;
using Newtonsoft.Json.Linq;
using System;
using System.IO;
using System.Net;
using System.Text;

namespace stackoverflow
{
class forstackoverflow
{
    public static int x = 0;
    public static JObject Jo { get; set; }

    static void Main()
    {

        string apiUrl = "REMOVED URL";
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(apiUrl);
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            Stream responseStream = response.GetResponseStream();
            StreamReader readerStream = new StreamReader(responseStream, Encoding.GetEncoding("utf-8"));
            string json = readerStream.ReadToEnd();
            readerStream.Close();
            var Jo = JObject.Parse(json);
            JArray id = (JArray)Jo["Results"];
            for (int x = 0; x < id.Count; x++)
            {
                string sqlstring = "sql already highlighted.";

                MySqlConnection Connection = null;
                const string ConnectionString = @"REMOVED CONNECTION DETAILS";
                Connection = new MySqlConnection(ConnectionString);
                MySqlCommand Command = new MySqlCommand(sqlstring, Connection);

                Command.Parameters.AddWithValue("@Name", (string)Jo["Results"][x]["name"]);
                Command.Parameters.AddWithValue("@Date", (string)Jo["Results"][x]["date"]);

                Connection.Open();
                Command.ExecuteNonQuery();
                Connection.Close();
                Connection.Dispose();

                Console.WriteLine((string)Jo["Results"][x]["name"] + " was inserted or updated in the database.");
            }
        }
    }
  }
}

If the date column is actually a date then parse the json's string value to a date. Also you should create your parameter to supply the dbtype instead of using AddWithValue. — Crowcoder
– Crowcoder, Commented Sep 6, 2018 at 10:53
The column is a DATETIME so I'd like to store both in the same column. Can you elaborate on the syntax for dbtype with an example, I'm still learning so I'm unable to formulate your use of dbtype. — H3ALY
– H3ALY, Commented Sep 6, 2018 at 11:17
I'm not as familiar with the MySQL provider but all ADO.Net providers implement the same interfaces. Look at the documentation for the MySqlParameter constructors, particularly the ones that take a MySqlDbType parameter. — Crowcoder
– Crowcoder, Commented Sep 6, 2018 at 12:28

845614720 · Accepted Answer · 2018-09-06 12:26:48Z

1

When I try to insert the DateTime 2018-03-03T11:00:00Z into my database (Which is an actual DATETIME type) I get the following error:

Warning: #1265 Data truncated for column 'date' at row 1

I assume your database is handling this error and just defaulting to 0000-00-00 00:00:00. MySQL DATETIME needs to be in the format of yyyy-MM-dd HH:mm:ss. That means that if you modify your example and remove the T and Z from it, as well as put a space in between the date and time then you can insert it into your Database.

You can put this in the correct format simply by using:

    var test = "2018-03-03T11:00:00Z";
    test = test.Replace("T", " ").Replace("Z", " ");

Hope this helps.

edited Sep 6, 2018 at 12:26

answered Sep 6, 2018 at 11:55

845614720

8582 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Crowcoder Over a year ago

Please don't advocate storing dates as strings. You can always format a date any way you want when displaying or reporting.

H3ALY Over a year ago

Thanks, will take a look into this. I'm guessing I need to rethink what i'm doing around the parse: var Jo = JObject.Parse(json);

845614720 Over a year ago

@Crowcoder I agree with you, I wasn't exactly advocating for it. I was just saying if OP really needed to have it in that format then DATETIME wouldn't be a storage option for him based on the restricted format of that type. In any case I removed it.

Collectives™ on Stack Overflow

Insert DateTime from JSON response in MySQL DB using C#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related