How to insert Datetimepicker Value in SQL Server database in custom format dd/MM/yyyy

Question

SqlCommand cmd = new SqlCommand("Insert into [f_present] Values   (@faculty_id, @name, @designation, @gender, @date, @status, @remarks)", con.active());

cmd.Parameters.AddWithValue("@faculty_id", fIdtxt.Text);
cmd.Parameters.AddWithValue("@name", fNametxt.Text);
cmd.Parameters.AddWithValue("@designation", fDestxt.Text);
cmd.Parameters.AddWithValue("@gender", fGendertxt.Text);
cmd.Parameters.AddWithValue("@date",dateTimePicker1.Text);
cmd.Parameters.AddWithValue("@status", comboBox1.Text);
cmd.Parameters.AddWithValue("@remarks", remarksTxt.Text);
cmd.ExecuteNonQuery();

I get this error:

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

A date is not a string. Use the DatetimePicker.Value property instead of the textual representation of that date. Moreover try to avoid the AddWithValue method as well. It has many problems — Steve
– Steve, Commented Jan 25, 2021 at 16:19
have you tried adding the date as... DateTime instead of as text? — Franz Gleichmann
– Franz Gleichmann, Commented Jan 25, 2021 at 16:19
Aside: specify column names in insert statement. AddWithValue is evil. And dispose your command and connection objects with using blocks — Charlieface
– Charlieface, Commented Jan 25, 2021 at 22:31

NineBerry · Accepted Answer · 2021-01-25 16:20:22Z

2

Don't use the text representation but the value.

cmd.Parameters.AddWithValue("@date", dateTimePicker1.Value);

answered Jan 25, 2021 at 16:20

NineBerry

28.8k4 gold badges68 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

derodevil · Accepted Answer · 2021-01-25 16:27:01Z

-2

You can change the date parameter with DateTimePicker value like this

cmd.Parameters.AddWithValue("@date",dateTimePicker1.Value.ToString("yyyy-MM-dd"));

answered Jan 25, 2021 at 16:27

derodevil

1,0991 gold badge19 silver badges47 bronze badges

4 Comments

NineBerry Over a year ago

This still uses a string with a fixed format. Some SQL database might not recognize this format and will fail. If you pass a DateTime variable directly, the middleware will choose the correct format for the used database

Bhaskar Roy Over a year ago

i tried this with ("dd/MM/yyyy") but showing the same error.

derodevil Over a year ago

@BhaskarRoy I think he uses SQL Server because he wrote SqlCommand. My answer should work in all SQL Server versions even if the client has different culture from the server

Panagiotis Kanavos Over a year ago

@derodevil this answer still uses the wrong type. There's absolutely no reason to convert the date into a string only to store it into a date-typed field. Besides, even yyyy-MM-dd is affected by the DATEFORMATl settings when stored into a datetime field. The newer types date, datetime2 and datetimeoffset aren't affected but datetime is

Collectives™ on Stack Overflow

How to insert Datetimepicker Value in SQL Server database in custom format dd/MM/yyyy

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related