-2

I'm trying to convert a data from excel to type of my field in database but I got the above error:

string query = "Insert into Réception_camions([Date d'arrivée],[heure d'arrivée],Poids_cam,Id_cam,Id_qualité) Values('" +DateTime.Parse(ds.Tables[0].Rows[i][0].ToString()) + "','" + /*TimeSpan.Parse*/(ds.Tables[0].Rows[i][1].ToString()) + "','" + ds.Tables[0].Rows[i][2].ToString() + "','" + ds.Tables[0].Rows[i][3].ToString() + "','" + ds.Tables[0].Rows[i][4].ToString() + "')";

Is there an other way to convert to datetime?

M.LOOK
  • 19
  • 2
  • 8
  • 5
    Have you heard of SQL Injection attacks? If you haven't you might want to research. You need to use parameterized queries, if you were to do this you will find that your other issues of "converting datetimes" would go away because ADO.NET is smart enough to handle this – maccettura Apr 26 '18 at 15:37
  • Almost none of that code should be needed. If you have a DS/DT you can update without that nasty SQL using the DataAdapter. All the net DB provider objects - DataTable. DataSet, DataAdapter, DBCommand - all know what a NET DateTime is and can save it without you molesting it – Ňɏssa Pøngjǣrdenlarp Apr 26 '18 at 15:42
  • Have you a simple example so I can use please. – M.LOOK Apr 26 '18 at 15:46

1 Answers1

0

SQL Server sometimes doesn't accept some strings where there is whitespace between the date and time values. You can workaround this by replacing whitespace with a 'T' character. 

--Failure
INSERT INTO TableName (DateTimeVal) VALUES ('2018-08-09 12:19:28')

--Success
INSERT INTO TableName (DateTimeVal) VALUES ('2018-08-09T12:19:28')

However, I would strongly recommend you follow the advice of the other answers here and find a better way to insert your data into the database

Joe van de Bilt
  • 225
  • 1
  • 3
  • 12
  • 1
    https://stackoverflow.com/questions/3144074/how-do-you-update-a-datetime-field-in-t-sql... If you recommend a date format please always stick with https://xkcd.com/1179/ (and not random suggestion to replace space with "T") – Alexei Levenkov Apr 26 '18 at 16:01
  • I'd be happy to upvote you if you can get this https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=1fc57eb2bf2188829cc0e07964a6dccb to work – John Cappelletti Apr 26 '18 at 16:17
  • You need to use dashes instead of slashes, my mistake, updated my answer – Joe van de Bilt Apr 26 '18 at 17:51
  • Also while I agree there is a standard for date parameters to follow the yyyy-mm-dd pattern, a SQL date time can accept a number of date formats, but needs a T character to indicate the start of the time part of the string. It isn't random. Read more here: https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017 listed under ISO 8601 – Joe van de Bilt Apr 26 '18 at 17:55
  • @JoeVandeBilt You overstate things. The letter "T" is not "needed" to convert succesfully so long as a recognized format is used. The "T" format is simply one of the recognized formats - corresponding to the ISO standard. There are others that can be successfully interpreted - such as the ODBC format. Tibor discusses this [here](http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes) – SMor Apr 26 '18 at 18:57
  • That's fair, I was merely responding that my answer was not a _random_ suggestion. – Joe van de Bilt Apr 26 '18 at 19:10