1

I want to insert the data in SQL database but in SQL query the date format can not be inserted. SQL query is the following:

string query1 = "INSERT INTO tbl_mngr_shift_handover_details(status_id,remark,inserted_by,inserted_date)VALUES('" + StatusDropDownList.SelectedValue + "','" + remarkTextArea.Content + "','" + Session["user_id"] + "','" + DateTime.Now + "')";

And it shows the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

So what can I do with the datetime format?

henser
  • 3,307
  • 2
  • 36
  • 47
P Mohite
  • 27
  • 5
  • 3
    Don't use a varchar type to store dates. Don't use string concatenation to create SQL queries. Google for `Bobby Tables`. Use parameterize queries – Panagiotis Kanavos Nov 21 '17 at 14:04
  • 1
    sqlserver <> mysql – S3S Nov 21 '17 at 14:05
  • 1
    It doesn't matter whether it's MySQL or SQL Server. SQL Injection works the same in both databases. The solution just as easy in both cases – Panagiotis Kanavos Nov 21 '17 at 14:05
  • @Panagiotis Kanavos I believe scsimon is referring to the fact that both databases are tagged in the OP's question. The syntax of the SQL can vary between the two. – Anthony T. Nov 21 '17 at 14:35
  • @AnthonyT. I know, but that's trivial compared to the actual problem. I could put `',NULL,NULL); DELETE FROM tbl_mngr_shift_handover_details;--` in that remarks field. Besides, the syntax is the same in this case - MySQL accepts named parameters with the `@` prefix – Panagiotis Kanavos Nov 22 '17 at 09:49

1 Answers1

2

Try DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

M.Y.Mnu
  • 718
  • 8
  • 22
  • Thank You so much. Its work – P Mohite Nov 22 '17 at 06:32
  • @PMohite no it doesn't. When I put `',NULL,NULL); DELETE FROM tbl_mngr_shift_handover_details;--` in the remarks field I get no error. I also get an empty screen. Fix your query. Use parameterized queries. Changing the sting format isn't a solution, it's simply a hack to bypass just *one* of the issues caused by string concatenation – Panagiotis Kanavos Nov 22 '17 at 09:48