error with AddDays() method c#

Question

i use the method adddays() to count date after 7 and 28 days from today or what ever date when im trying to insert the values into database table fields currect_date and crush_date date type fields it shows that adddays(28) value is not a correct date/time value.

DateTime d = new DateTime();
        d = DateTime.Today;
        cmd = new SqlCommand("insert into sq_crush (currect_date,crush_date) values ('" + d.AddDays(7).ToShortDateString() + "','" + d.AddDays(28).ToShortDateString() + "')", cn);
        if (cn.State == ConnectionState.Closed)
            cn.Open();
        // MessageBox.Show(dd);
        cmd.ExecuteNonQuery();

if i changed 28 to 14 it works without any troubles but if i used any value over 14 it shows the same error "Conversion failed when converting date and/or time from character string."
How to make it work as it works with values under 14 and if there any other way to get dates after 28 or 23 or any number of days after one specific date.

In general I want to insert the date in three fields first one today second is the date after 7 days from the first date which I have no problems with this and the last one the date after 28 days Please help me

Read the error message - what makes you think changing the `AddDays()` value has anything to do with the conversion? Why are you passing strings for date values? — Ňɏssa Pøngjǣrdenlarp, Dec 29 '17 at 20:50

John Wu · Accepted Answer · 2017-12-29T21:43:40.430

This is the sort of problem when you pass dates as a string. You don't necessarily know what the correct format is, and it sounds like your SQL Server doesn't agree where the month should go (in first position or second position within the string); when you provide a day that is too high to be a month, SQL Server throws an error.

You can avoid the whole problem (and other problems too) by passing the date as a DateTime parameter, like so:

DateTime d = DateTime.Today;
var sql = "insert into sq_crush (currect_date,crush_date) values (@date1,@date2)";
var cmd = new SqlCommand(sql, cn);
cmd.Parameters.AddWithValue("@date1", d.AddDays(7));
cmd.Parameters.AddWithValue("@date2", d.AddDays(28));
if (cn.State == ConnectionState.Closed)
    cn.Open();
cmd.ExecuteNonQuery();

As a rule, it is a bad idea to concatenate strings to form SQL (link).

score 0 · Answer 2 · answered Dec 29 '17 at 20:50

0

That is because 14 days will put you beyond the 12th. So, it's assuming you are providing a different formatting than the one SQL server thinks you are giving it.

If you choose not to use parameterized queries, then your date format should match what SQL server is expecting.

answered Dec 29 '17 at 20:50

Ctznkane525

7,297
3
16
40

beyond the 12th what? – Rufus L Dec 29 '17 at 21:13
well...there's only 12 months in a year. So, SQL server must think your days are months to bomb after a value of 12. That's why you should format the date or use a parameterized query. You can use ToString with a format to match the format SQL server is expecting, usually ToString("yyyy-MM-dd") – Ctznkane525 Dec 29 '17 at 21:23
keep in mind, 15 days from now is january 13th. but 14 days from now is january 12th, that's why you have a problem beyond 14 days, because it thinks the 13 days is a month field instead of a day field. – Ctznkane525 Dec 29 '17 at 21:24
If it takes the 14 as a month it will change the year automatically to 2019 that’s not the problem I use the d.adddays(28) as a string value for message and label.text or whatever it shows the date correctly after 28 days but when I tried to insert it to dB it shows the error – shiroi Dec 30 '17 at 10:58

error with AddDays() method c#

2 Answers2