0

I have a string which is got from excel which is of the format dd/mm/yyyy hh:mm:ss The SQL table I have takes smalldatetime and uses the mm/dd/yyyy hh:mm:ss way. I have tried the below to convert the format. The below code has error and is not able to convert to date time

//data is coming from dataTable. dataTable is filled from Excel using OLEDB 
if (row[5].ToString().Contains('/'))
{
    string[] birthday = row[5].ToString().Split('/');
    int month, day;
    int.TryParse(birthday[0], out day);
    int.TryParse(birthday[1], out month);

    string newda = month + "/" + day + "/" + birthday[2];
    DateTime dt = Convert.ToDateTime(newda, System.Globalization.CultureInfo.InvariantCulture.ToString("MM/dd/yyyy hh:MM:ss");
    row[5] = dt;
}

what am I missing ? How to fix this?

Soheila Tarighi
  • 487
  • 4
  • 15
Venkat
  • 89
  • 12
  • Do you have access to Date of Birth? – Ross Bush Apr 05 '20 at 04:36
  • yes, row[5] contains data like 22/11/2019 00:00:00 – Venkat Apr 05 '20 at 04:37
  • There is no need to mess around with rearranging the string. Just tell .NET what the format is. See marked duplicate. – Peter Duniho Apr 05 '20 at 04:54
  • I tried this as well - row[5] = DateTime.ParseExact(row[5].ToString(), "MM-dd-yyyy hh:mm:ss", CultureInfo.InvariantCulture); But getting same error – Venkat Apr 05 '20 at 05:14
  • 1
    If the format you're getting is dd/MM/yyyy HH:mm:ss, why would you expect MM-dd-yyyy hh:mm:ss to work? I also don't believe that your SQL table uses any particular format. – ProgrammingLlama Apr 05 '20 at 05:16
  • @John - Thanks. Not getting a way to save the date in the mm format which I require. what am i missing – Venkat Apr 05 '20 at 05:18
  • 2
    For one, `DateTime` doesn't have any format whatsoever. The second parameter of `ParseExact` is the expected format of the date you're trying to parse. The datetime value is stored as a `long`. Second, if your database requires a format, you're not interacting with the database correctly. You shouldn't include values as part of your SQL command string, you should use `SqlParameter`. – ProgrammingLlama Apr 05 '20 at 05:19
  • Thanks. I removed all the datetime formats and passed the data as is. In the sql ExecuteNon query, i get the error as - The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value – Venkat Apr 05 '20 at 05:23
  • I meant you should parse your data to a `DateTime` and then pass that using an `SqlParameter`. – ProgrammingLlama Apr 05 '20 at 05:24
  • @John - could you please provide a sample code – Venkat Apr 05 '20 at 05:25
  • I tried this: string newdate = month + "/" + day + "/" + birthday[2]; DateTime dt = Convert.ToDateTime(newdate.Trim()); // getting error in this line. The string newdate contains data as - 11/22/2019 00:00:00 – Venkat Apr 05 '20 at 06:40

0 Answers0