ISO 8601 to Standard Date

[M1kkelZU]

Hey guys, I'm figuring out how I can convert an ISO 8601 to a standard DateTime like dd/mm/yyyy.

The string is:

2013-01-03T00:00:00.0000000Z

And I'd want it to show it like so:

03-01-2013

I'm storing this in an SQLite Database and the code I use to store everything:

conn.Open();
 
                                command.Connection = conn;
                                command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
 
                                command.Parameters.Add("@Date", DbType.String);
                                command.Parameters.Add("@LogName", DbType.String);
                                command.Parameters.Add("@Channel", DbType.String);
                                command.Parameters.Add("@DateRecord", DbType.DateTime);
                                command.Parameters.Add("@SizeInBytes", DbType.Int64);
 
                                foreach (DataGridViewRow row in dgvTable.Rows)
                                {
                                    if (!row.IsNewRow)
                                    {
                                        command.Parameters["@Date"].Value = row.Cells[0].Value;
                                        command.Parameters["@LogName"].Value = row.Cells[1].Value;
                                        command.Parameters["@Channel"].Value = row.Cells[2].Value;
                                        command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
                                        command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
                                        command.ExecuteNonQuery();
 
                                        btnGetDB.Enabled = false;
                                        btnOpenLog.Enabled = false;
 
                                        txtFilterChannel.Enabled = false;
                                        txtFilterDate.Enabled = false;
                                        txtFilterLog.Enabled = false;
                                        Application.DoEvents();
                                    }
                                    command.CommandType = CommandType.Text;
                                }

the Visual aspect is that I use a datagridview to see the data, so the conversion would have to happen while I read and store the data in the database.

Any help would be appreciated.

Michael

[Rabbit]

Correct me if I'm wrong but SQLite doesn't have any date data types. You have to use the functions they provide to convert to and either a string or numerical representation.

[M1kkelZU]

That is true but I'm not using SQLite functions to convert the dates. I've seen some examples of ISO to a standard date but for some reason I can't use them while importing and writing data to the database.

[vijay6]

Hey M1kkelZU, you can use the following code to convert 'ISO 8601' date to normal date format. After this you can store the normal date in your database.

string isoDate = "2013-01-03T00:00:00.0000000Z";
 
int year = Convert.ToInt32(isoDate.Split('-')[0]);
int month = Convert.ToInt32(isoDate.Split('-')[1]);
int day = Convert.ToInt32((isoDate.Split('-')[2]).Split('T')[0]);
 
string Date = null;
 
Date = day < 10 ? "0" + day + "-" + (month < 10 ? "0" + month + "-" + year : month + "-" + year) : (day + "-") + (month < 10 ? "0" + month + "-" + year : month + "-" + year);
 
MessageBox.Show(Date);

[M1kkelZU]

ok thanks, I might have forgotten to mention that I'm reading the string from a file where I read and split each "cell" of data. But this helps with getting it started. Thanks :)