CODEWITHSUNDEEP
c#sql-server-2008
user1490374
user1490374

Reputation: 65

How to compare date from database using C#?

I would like to compare the date selected from the database (every entry in EndDate) and compare them with today date. Is there any way to do this programmatically? Like extracting the dates and comparing them individually? I need this because I need to update the status for the table.

string username;
username = HttpContext.Current.User.Identity.Name;
string date = DateTime.Now.ToString("MM/dd/yyyy");
txtDate.Text = date;


SqlConnection conn1 = 
    new SqlConnection("Data Source=mydatasource\\sqlexpress;" 
                     + "Initial Catalog = Suite2; Integrated Security =SSPI");
SqlDataAdapter adapter;
string end;
end = "SELECT EndDate FROM Table_Message WHERE username = '" + username + "'";
adapter = new SqlDataAdapter(end, conn1);
conn1.Open();
DataSet ds = new DataSet();
adapter.Fill(ds);
//Execute the sql command
GridView2.DataSource = ds;
GridView2.DataBind();
conn1.Close();

Upvotes: 0

Views: 2968

Answers (4)

Chaitanya
Chaitanya

Reputation: 5293

I would just modify the SQL query to do the comparison there rather than in code.

Replace your SQL with this.

end = "SELECT EndDate, " +
      "case when EndDate >= CURRENT_TIMESTAMP then 'Expired' " +
      "when StartDate <= CURRENT_TIMESTAMP then 'Pending' " +
      "else 'NotExpired' " +
      "end " +
      "as MessageStatus " +
      "FROM Table_Message WHERE username = '" + username + "'";

This should return two columns. One of EndDate and the other of whether it is before or after today.

EDIT

I realised that you want to update a table in the database not just return it. In that case you will need to execute a sql statement to update the appropriate columns and then select them. For example

 SqlCommand updateDatabase = new SqlCommand();
 updateDatabase.CommandText = "update dbo.Table_Message set Message_Status = case when EndDate >= CURRENT_TIMESTAMP then 'Expired' when StartDate <= CURRENT_TIMESTAMP then 'Pending' else 'NotExpired'";
 updateDatabase.ExecuteNonQuery();

You can then just select the status directly from your original query sql string

end = "SELECT EndDate, Message_Status " +
      "FROM Table_Message WHERE username = '" + username + "'";

Having said that. I would very strongly suggest you shouldn NOT be doing it this way. You should not be updating/storing something like the current status in the database. Each time you do the update in your case, you will be doing a very expensive database operation. Not only that, the meaning of the column changes every second, every millisecond. What is an "Expired" message really depends on the day you check. This sort of data should NOT be stored in the database. You should be interpreting which message is expired or and which is not each time. You can do that in C# code or as part of the SQL query like I originally suggested. You should not have a column in your database whose values can potentially change every second. That is my opinion.

Hope that helps.

Upvotes: 1

AnandPhadke
AnandPhadke

Reputation: 13506

   end = "UPDATE Table_Message SET message_status=CASE WHEN EndDate>GETDATE() then 'Expired' WHEN StartDate<GETDATE() then 'Pending' else 'Ongoing' END WHERE username = '" + username + "'";

Upvotes: 1

Subhash Lama
Subhash Lama

Reputation: 433

My opinion

First if all. Are you going to use this code on client side or server side. You can compare the dates on sql server query also using datediff function. That way you will get exact comparision if the server and client clocks are not synchronized and you are planning to do it on client

Upvotes: 0

jack.li
jack.li

Reputation: 973

First, you need to get the date from the database.

DateTime dat1 = DateTime.Parse(2003-12-30); 
DateTime dat2 = DateTime.Parse(2004-12-30); 

TimeSpan spanTime=(dat1-dat2);

you can use SpanTime for comparison, like spanTime.Days.

Upvotes: 0

Related Questions