Convert string to DateTime in C# and write to SQL

Question

I am trying to convert my string to DateTime and write it to a datetime field in my table in SQL.

string dateTimeString = line.Substring(5, 14);
var date = DateTime.ParseExact(dateTimeString, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");

SqlCommand command = new SqlCommand("INSERT INTO dbo.ODCZYTY (numer_karty,data, rodzaj,obszar,kierunek) VALUES(@numer_karty,@data, @rodzaj, @obszar, @kierunek)", conn);

command.Parameters.AddWithValue("@numer_karty", line.Substring(0, 5));
command.Parameters.AddWithValue("@data", date);
command.Parameters.AddWithValue("@rodzaj", line.Substring(19, 2));
command.Parameters.AddWithValue("@obszar", line[line.Length - 2]);
command.Parameters.AddWithValue("@kierunek", line[line.Length - 1]);

line.Substring(5, 14) = 20160127122843 But i get this:

Conversion failed when converting date and/or time from character string

What am I doing wrong?

Which RDBMS (product and version)? You should **never** rely on culture specific date literals... Use ISO 8601, ODBC or Universal Format [Read this for examples](http://stackoverflow.com/a/34275965/5089204) — Shnugo, Apr 07 '16 at 13:37
If you are not interested in the time, why not change the `(5,14)` to `(5,8)`? SQL Server at least would cast this implicitly... — Shnugo, Apr 07 '16 at 13:42
Because i need date and time. 20160127122843 = date 2016 01 27 and time 12 28 43 — Code Obiect, Apr 07 '16 at 14:04
With your (wrong) `ParseExact` call, you seem to read the date only. I just added an UPDATE to my answer — Shnugo, Apr 07 '16 at 14:11

score 4 · Answer 1 · answered Apr 07 '16 at 13:29

4

Do not convert your string to DateTime and then back to string just for the sake of passing it through SQL parameters. Your column in table probably expects a DateTime value and since you are adding it with AddWithValue it would consider the value to be string and not DateTime. Simple leave the parsedDateTimeobject asDateTimeand then you can useAddWithValue` like:

var date = DateTime.ParseExact(dateTimeString, "dd/MM/yyyy", CultureInfo.InvariantCulture);
//and while adding paramter
command.Parameters.AddWithValue("@data", date);

(Also use CultureInfo.InvariantCulture instead of null to parse DateTime, you could run into issues with cultures where the default separator in DateTime is different from /)

One other option is to specify DB type explicitly with Add like:

command.Parameters.Add(new SqlParameter("@data", SqlDbType.DateTime) {Value = date});

answered Apr 07 '16 at 13:29

Habib

219,104
29
407
436

I use It:command.Parameters.Add(new SqlParameter("@data", SqlDbType.DateTime) {Value = date}); And i get this: You can not convert the string to DATATIME – Code Obiect Apr 07 '16 at 13:34
@CodeObiect Did you take away the final `ToString()`? If you pass in the date as typed date no conversion is necessary... – Shnugo Apr 07 '16 at 13:38
line.Substring(5, 14) = 20160127122843 and it i must write to SQL. – Code Obiect Apr 07 '16 at 13:42
@CodeObiect, do not convert date to string. Notice the first line of code in the answer, it is creating a `DateTime` object, not a string. Use that. – Habib Apr 07 '16 at 13:44
But i want convert string to date. – Code Obiect Apr 07 '16 at 13:45
@CodeObiect, why do you want to do that ? the error is clearly indicating that your column in table is not string. What is the actual type of column in your table ? – Habib Apr 07 '16 at 13:46
SQL type column: datetime – Code Obiect Apr 07 '16 at 13:50
Beacuse as you dont convert I get "Conversion failed when converting date and/or time from character string" – Code Obiect Apr 07 '16 at 14:01
@Habib I think the problem is not while passing a value but earlier while parsing the string. If OP wants to use `ParseExact` the given string should fit to the given format string. This seems to be the reason for the error... – Shnugo Apr 07 '16 at 14:06
When I try (for testing) add to SQL DateTime.Now it work, and write me date and time to SQL table. – Code Obiect Apr 07 '16 at 14:08
@CodeObiect This is clear because in this case you pass in a valid datetime. You are failing **before** this on parsing your string... – Shnugo Apr 07 '16 at 14:13

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

You try to parse a string like this

line.Substring(5, 14) = "20160127122843"

with

DateTime.ParseExact(dateTimeString, "dd/MM/yyyy", null)

How should this work? Try this

string dateTimeString = line.Substring(5, 8); //just 8 to get the date only
var d = DateTime.ParseExact(dateTimeString , "yyyyMMdd", CultureInfo.InvariantCulture);

After this do not reconvert the date with ToString(). Just pass it to the parameter as typed date.

UPDATE

If you need the time, keep the (5,14) and try this

var d = DateTime.ParseExact(dateTimeString , "yyyyMMddHHmmss", CultureInfo.InvariantCulture);

Yes I do This! Thx! You made my day :) – Code Obiect Apr 07 '16 at 14:25 — Code Obiect, Apr 07 '16 at 14:25

score 0 · Answer 3 · answered Apr 07 '16 at 13:29

0

Convert it to ISO-8601 format (yyyy-mm-dd etc) for complete clarity

"MM/dd/yyyy" only works on SQL Server installs that have us-english setting.

answered Apr 07 '16 at 13:29

gbn

422,506
82
585
676

Convert string to DateTime in C# and write to SQL

3 Answers3

UPDATE