string was not recognized as a valid datetime using ParseExact

Question

I'm trying to get data from angular and save it in database through WebAPI using C#. Now I have a problem will converting DOB , I get this error

"string was not recognized as a valid datetime"

Angular date Format : "Wed Jan 05 2022 00:00:00 GMT+0530 (India Standard Time)"

Database date format : "YYYY-MM-DD"

Now need to convert into this format.

string dob = httprequest.Form.Get("dob");

MySqlConnection myConnection = new MySqlConnection();
myConnection.ConnectionString = @"Data Source=localhost; Database=pramod; User ID=itesuser; password=ites; Port=3309";
            
MySqlCommand sqlCmd = new MySqlCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "Insert Into emp_ang (DOB) Values (@dob)";
sqlCmd.Connection = myConnection;

DateTime birth = DateTime.ParseExact(dob, "yyyy/MM/dd", CultureInfo.InvariantCulture);
sqlCmd.Parameters.AddWithValue("@dob", birth);
myConnection.Open();
int rowInserted = sqlCmd.ExecuteNonQuery();
myConnection.Close();
return Ok("inserted");

Answer 1

First of all, I'm so glad to see you don't try to save your DateTime as a string in your database. Don't ever trapped yourself with choosing the wrong data type. MySQL have DATE and DATETIME types, choose which one fits for your needs.

Let's look at your string: "Wed Jan 05 2022 00:00:00 GMT+0530 (India Standard Time)"

There is no built-in way to parse time zone names in .NET. So, it will be better to delete that time zone name - (India Standard Time) part - in your string.

Let's say we have: ""Wed Jan 05 2022 00:00:00 GMT+0530"

Still we have "GMT" and still there is no way to parse it without manipulating your string or put it that as a custom part in your format. And you can parse your UTC offset part with zzz custom format specifier. One last thing, since your string have UTC Offset, it would be better to parse it to DateTimeOffset instead of System.DateTime.

var dob = "Wed Jan 05 2022 00:00:00 GMT+0530";
var birth  = DateTimeOffset.ParseExact(dob, 
                "ddd MMM dd yyyy HH:mm:ss 'GMT'zzz",
                 CultureInfo.InvariantCulture,
                 DateTimeStyles.None);

Now we have 1/5/2022 12:00:00 AM +05:30 as a birth variable. You can even use it's .LocalDateTime, .DateTime or .UtcDateTime properties which depends on which value you save in your database.

Other than that, a few things to consider;

• Don't put your connection string to your code as a plain text, read: Where Should I Store a database Connection String?
• Also use using statement to dispose your connection and command automatically since they are IDisposable.
• As a best practice, don't use AddWithValue method. It may generate unexpected results. Use .Add() method or it's overloads.

Like;

using(var myConnection = new MySqlConnection(connectionString))
using(var sqlCmd = myConnection.CreateCommand())
{
     // Creater your command
     // Add your parameter and it's value
     // Open your connection
     // Execute your query
}