Send date from C# to SQL Server

Question

I have issue with converting datetime in C# to date in SQL Server.

I constantly get this error message:

input string was not in correct format.

I tried this query

insert into TasksCopy (Start_Date) 
values (CONVERT(date, '10/01/2017'));

It worked in SQL Server Management Studio, but didn't work using C# program in Visual Studio.

However the query below worked in both Visual Studio and Management Studio:

insert into TasksCopy (Time) 
values (CONVERT(time, '17:00:00'));

I tried this way using my C# app to send datetime variable called datetime:

insert into TasksCopy (Start_Date) 
values (CONVERT(date, datetime.date.ToString("mm/dd/yyyy")));

however that still did not work.

The code below did not work either:

string query = $"insert into TasksCopy (Start_Date) values (CONVERT(date, @date));";

SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@date", dateTime.Date);

That also did not work.

What is the correct way to send data to SQL Server as date data type?

Thanks in advance

if you want to add the insert date, you can call SQL GetDate() for instance.. — rmjoia, Oct 01 '17 at 15:10
You are missing the point. Pass the values in as parameters. The parameters will take care of the types. — Gordon Linoff, Oct 01 '17 at 15:18
i can send time without using CONVERT but still it is not working for sending date — Mustafa Alsamarrai, Oct 01 '17 at 15:29

score 0 · Accepted Answer · answered Oct 01 '17 at 15:12

0

date managing is always a pain no matter what language, but somehting that seems to always work in SQL engines is to send the date string as

yyyy-mm-dd

So you can try something like this:

insert into TasksCopy (Start_Date) values (CONVERT(date, datetime.date.ToString("yyyy-mm-dd")));

answered Oct 01 '17 at 15:12

cmelgarejo

59
5

1

It will work by just passing the date in the parameter. You do not need to `ToString()` it into a specific format. – CodingYoshi Oct 01 '17 at 15:15
heh, the accepted answer gets a -1, but meanwhile it helps the OP, doesn't matter :) – cmelgarejo Oct 02 '17 at 17:57
1

Getting the right answer that matters after all. – Mustafa Alsamarrai Oct 02 '17 at 18:03
Glad it help you man, I wish you success in taming the C#+MSSQL beast – cmelgarejo Oct 02 '17 at 18:10

Send date from C# to SQL Server

1 Answers1