delete fields from sql database where the date is a previous date to today in c#

Question

SqlConnection sqlcon = new SqlConnection(GlobalClass.DBLocate);
sqlcon.Open();

string query = "Delete from [Plans] Where Date < '" + 
GlobalClass.Date.ToShortDateString() + "'";

SqlDataAdapter sda = new SqlDataAdapter(query, sqlcon);

sda.SelectCommand.ExecuteNonQuery();

I have tried many techniques but everything in this database is deleted or nothing at all. Many thanks

Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Apr 06 '18 at 13:27
You should format your date accordingly to the regional settings or ansi std YYYYMMDD — McNets, Apr 06 '18 at 13:30

score 0 · Accepted Answer · answered Apr 06 '18 at 14:20

Instead of passing in the date as a string, why don't you write the SQL using it's native date functions?

As this question is tagged as C# and not with any specific flavor of SQL, I am writing this connecting to SQL Server. You also did not provide the DDL for the table that is being deleted from. The code is based on using a proper column type for the [Date] column, which would be some variant of DateTime.

The following statement should delete everything up the current DateTime

string query = "DELETE from [Plans] WHERE Date < GetDate()";

If you need to fine tune this to everything before today, then you can use CAST/CONVERT and just get the Date portion:

string query = "DELETE from [Plans] WHERE Date < Cast(GetDate() as Date)";

You also don't need to use a DataAdapter for a NonQuery; it's return type is an int, and can be assinged directly

int RowsDeleted = cmd.ExecuteNonQuery();

So putting it all together, we get this code block. I also wrapped within a using block so that the resources are properly cleaned up afterwards.

int RowsDeleted = -1;

using (SqlConnection sqlcon = new SqlConnection(GlobalClass.DBLocate)) {
    string query = "DELETE from [Plans] WHERE Date < GetDate()";
    SqlCommand cmd = new SqlCommand(query, sqlcon);

    sqlcon.Open();
    RowsDeleted = cmd.ExecuteNonQuery();
    sqlcon.Close();
}

score -1 · Answer 2 · answered Apr 06 '18 at 13:35

-1

Try this. I guess your date value is to new, and hence deletes everything.

SqlConnection sqlcon = new SqlConnection(GlobalClass.DBLocate);
sqlcon.Open();

DateTime yesterday = DateTime.Today.AddDays(-1);

string query = "Delete from [Plans] Where Date <= '" + 
yesterday.ToShortDateString() + "'";

SqlDataAdapter sda = new SqlDataAdapter(query, sqlcon);

sda.SelectCommand.ExecuteNonQuery();

answered Apr 06 '18 at 13:35

andreasnico

1,478
14
23

This code is still deleting the date rows with dates such as 20/04/2018 – Tommy Lydon Apr 06 '18 at 13:43
1

Can you construct a sql that does the correct thing before you try to do the same in c#? Then compare and see what the differences are? – andreasnico Apr 06 '18 at 13:47
query = "Delete from [Plans] Where Date < 06/04/2018" – Tommy Lydon Apr 06 '18 at 13:50
No @TommyLydon . Read https://stackoverflow.com/questions/9121332/passing-date-and-time-to-sql-server-using-c-sharp . – mjwills Apr 06 '18 at 13:54
@mjwills I still can't get it to work, what should I actually write? – Tommy Lydon Apr 06 '18 at 14:15

delete fields from sql database where the date is a previous date to today in c#

2 Answers2