Insert DateTime into SQL DateTime

Question

I am trying to insert a DateTime into my SQL Server table column of type DateTime2(7) using the INSERT INTO syntax.

The error I receive is:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Conversion failed when converting date and/or time from character string.

I initialize the DateTime variable with:

DateTime TaskCreatedDate = DateTime.Now;

and use it here:

insertTaskCmd.Parameters.AddWithValue("@TaskCreatedDate", TaskCreatedDate);

Any thoughts?

Update:

I have tried:

insertTaskCmd.Parameters.Add("@TaskCreatedDate", SqlDbType.DateTime2).Value = TaskCreatedDate;

with the same error being produced.

Full query:

            string insertTask = String.Format("INSERT INTO [Tasks] VALUES (@TaskNumber, '@TaskCreatedDate', '', '', '', @Status, '3', '')");
            SqlCommand insertTaskCmd = new SqlCommand(insertTask, conn);
            insertTaskCmd.Parameters.AddWithValue("@TaskNumber", newTaskNumbers[i]);

            insertTaskCmd.Parameters.Add("@TaskCreatedDate", SqlDbType.DateTime2).Value = TaskCreatedDate.ToString("yyyy-MM-dd HH:mm:ss");

            insertTaskCmd.Parameters.AddWithValue("@Status", 0); // 0 is Active
            insertTaskCmd.ExecuteNonQuery();

possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — mason, Mar 11 '15 at 16:16
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Mar 11 '15 at 16:18
a handy replacement for `AddWithValue` is `Parameters.Add(new SqlParameter("@paramName", SqlDbType.WhatEver, columnSize) { Value = valueVariable };`. When you always specify the same size for character columns, you will produce only one execution plan. When you leave it out, you end with one plan for each parameter value length combination you are using in the query. — eFloh, Mar 11 '15 at 16:21
I'm extremely glad I've read that, but in this case, it didn't solve the issue. Thanks — Lighty, Mar 11 '15 at 16:22
Great tip by @eFloh. Could you maybe also specify what you are doing? Are you called a Stored Procedure or using default INSERT INTO syntax? Either way, could you perhaps include the query you are using? — romatthe, Mar 11 '15 at 16:23
you should tell us what the actual datatype of the `TaskCreatedDate` is it a DateTime or a TimeStamp — MethodMan, Mar 11 '15 at 16:25
also is this a typo `insertTaskCmd.Parameters.Add("@TaskCreatedDate", SqlDbType.DateTime2).Value` the `DateTime2`...?? @Lighty please show what the stored proc or query looks like as well as the Table Schema in regards to the dataype of the field. it will help in eliminating any and all guess work from us — MethodMan, Mar 11 '15 at 16:26
I tried it with DateTime and DateTime2, both are valid SQL types — Lighty, Mar 11 '15 at 16:28
@jean the easiest thing to do is to not change it to a varchar(30) the simplest thing to do for the OP is to tell us what the actual datatype is for that column as well as the query — MethodMan, Mar 11 '15 at 16:31
first problem I see is in the query.. why are you not defining the exact fields that you want to insert into.. try changing the query and I bet it will work.. also if you are going to post code..please show how all relevant values in regards to your issue are used, populated and or defined `newTaskNumbers` ...?? refactor your query to use `Parameterized query` wrap all the Sql objects around a using. as well, wrap all Executing attempts around a `try{}catch{}` you are assuming that your query will always run without failure — MethodMan, Mar 11 '15 at 16:32

score 4 · Answer 1 · edited Jul 29 '20 at 18:27

I think your insert query is just wrong - you have the @TaskCreatedDate in single quotes - that shouldn't be .....

Try this:

string insertTask = "INSERT INTO [Tasks] VALUES (@TaskNumber, @TaskCreatedDate, '', '', '', @Status, '3', '')";

SqlCommand insertTaskCmd = new SqlCommand(insertTask, conn);
insertTaskCmd.Parameters.Add("@TaskNumber", SqlDbType.Int).Value =  newTaskNumbers[i];
insertTaskCmd.Parameters.Add("@TaskCreatedDate", SqlDbType.DateTime2).Value = TaskCreatedDate;
insertTaskCmd.Parameters.Add("@Status", SqlDbType.Int).Value = 0; // 0 is Active

insertTaskCmd.ExecuteNonQuery();

Note: I would also recommend to always explicitly specify the columns you want to insert into:

string insertTask = @"INSERT INTO [Tasks](TaskNumber, TaskCreatedDate, .., .., .., Status, .. ..)
                      VALUES (@TaskNumber, @TaskCreatedDate, '', '', '', @Status, '3', '')");

score 0 · Accepted Answer · answered Mar 11 '15 at 16:35

0

Fixed.

Note around the @TaskCreatedDate there are single quotation marks ('). Remove these and it's solved.

answered Mar 11 '15 at 16:35

Lighty

7
8

1

you need to remove all quoted text and start using / learning how to use parameters.. as well as the debugger.. respectfully speaking – MethodMan Mar 11 '15 at 16:36
@MethodMan: That seems uncalled for. The OP does use parameters. Some of the inserted values happen to be constants, hence the need to use quotes. – Andriy M Mar 13 '15 at 08:59
@AndriyM perhaps you missed this part in the OP's original question `'@TaskCreatedDate'` – MethodMan Mar 13 '15 at 15:09

Insert DateTime into SQL DateTime

2 Answers2