Update SQL database date column

Question

i have SQL server database and table with column for date (date datatype). I would like to change the value of date column for all records to have todays date, but something is not working, please help. My code:

 con.Open();
 DateTime date = DateTime.Now.Date;
 string insStr = "UPDATE tblBusiness SET DateCreated = @Date;";
 SqlCommand updateDate = new SqlCommand(insStr, con);
 updateDate.Parameters.AddWithValue("Date", date);
 updateDate.ExecuteNonQuery();
 con.Close();

Thanks, Oak

EDIT: Thanks for the answers. The thing why it was not working was because of too many records in database and the default update timeout is 30sec or something. And thats why no record was changed. updateDate.CommandTimeout = 0; fixed the problem.

Thanks for the answers. The thing why it was not working was because of too many records in database and the default update timeout is 30sec or something. And thats why no record was changed. updateDate.CommandTimeout = 0; fixed the problem. — Oak
– Oak, Commented May 11, 2012 at 14:39

Arion · Accepted Answer · 2012-05-11 14:18:10Z

5

I think this line:

updateDate.Parameters.AddWithValue("Date", date);

Should be:

updateDate.Parameters.AddWithValue("@Date", date);

answered May 11, 2012 at 14:18

Arion

31.3k10 gold badges74 silver badges89 bronze badges

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

Comments

hwcverwe · Accepted Answer · 2012-05-11 14:18:41Z

2

use @Date instead of Date

updateDate.Parameters.AddWithValue("@Date", date);

answered May 11, 2012 at 14:18

hwcverwe

5,3677 gold badges37 silver badges64 bronze badges

Comments

Dennis Traub · Accepted Answer · 2012-05-11 14:19:10Z

1

//Replace
updateDate.Parameters.AddWithValue("Date", date);

// with (watch the @-symbol)
updateDate.Parameters.AddWithValue("@Date", date);

answered May 11, 2012 at 14:19

Dennis Traub

51.9k7 gold badges100 silver badges113 bronze badges

Comments

Gratzy · Accepted Answer · 2012-05-11 14:19:31Z

1

try

updateDate.Parameters.AddWithValue("@Date", date);

answered May 11, 2012 at 14:19

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

Comments

4b0 · Accepted Answer · 2012-05-11 14:21:41Z

0

Replace Date with @Date.

updateDate.Parameters.AddWithValue("@Date", date);

answered May 11, 2012 at 14:21

4b0

22.4k30 gold badges97 silver badges143 bronze badges

Comments

Oak · Accepted Answer · 2012-05-13 16:20:50Z

0

Thanks for the answers. The thing why it was not working was because of too many records in database and the default update timeout is 30sec or something. And thats why no record was changed. updateDate.CommandTimeout = 0; fixed the problem.

answered May 13, 2012 at 16:20

Oak

1,1793 gold badges13 silver badges21 bronze badges

Comments

Internet Engineer · Accepted Answer · 2012-05-11 14:55:48Z

-1

This should work. You don't need a where clause if you are updating all records.

con.Open();
 DateTime date = DateTime.Now.Date;
 string insStr = "UPDATE tblBusiness SET DateCreated = " + date;
 SqlCommand updateDate = new SqlCommand(insStr, con);
 updateDate.ExecuteNonQuery();
 con.Close();

edited May 11, 2012 at 14:55

answered May 11, 2012 at 14:19

Internet Engineer

2,54410 gold badges41 silver badges54 bronze badges

2 Comments

Kendall Frey Over a year ago

You had better have one (unless you want to update all records)

Internet Engineer Over a year ago

You guys are correct. I misread the question. But I have revised my answer.

Collectives™ on Stack Overflow

Update SQL database date column

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related