Comparing SQL Server DATETIME with DateTime.NOW in C# / ASP.NET

Question

How do I compare a SQL Server DATETIME with the DateTime.Now value? As you can see I assigned it to a Session and tried comparing it with DateTime.Now.

string timestamp = @"SELECT sr.*, ud.* FROM SuspensionRecord sr, UserData ud WHERE sr.User_ID=@User_ID AND ud.User_ID=@User_ID";

using (SqlCommand cmd2 = new SqlCommand(timestamp, con))
{
    cmd2.Parameters.AddWithValue("@User_ID", Session["UserID"].ToString());

    using (SqlDataReader dr = cmd2.ExecuteReader())
    {
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                Session["suspensiondate"] = dr["End_Date_Suspension"].ToString();
            }

            if (Convert.ToDateTime(Session["supensiondate"]) >= DateTime.Now.Date)
            {
                lblMessage.Text = "The account's status is suspended.";
                lblMessage.Visible = true;
            }
        }        
    }
}

May I suggest only selecting the necessary column instead of multiple .*'s in your initial query. There is no reason to pull back all that data if you are only using one column's data — Ryan Wilson, Nov 05 '18 at 16:02
Use a parameter of the proper type (see duplicate). Then just do a normal compare: `WHERE tblPrefix.DateColumn > @dateVariable`. — Igor, Nov 05 '18 at 16:03
I think there is a typo in the line comparing session date with DateTime.Now. It should be `Session["suspensiondate"]` not `Session["supensiondate"]`. — haldo, Nov 05 '18 at 16:05
@Igor - The dup doesn't look related to the question. The one you linked to is about passing user input into a sql string, and using proper parameratized queries. This one is more about properly using data that is returned from a query. I see the problem here, is that the OP is abusing `.ToString()` and relying on parsing via `Convert.ToDateTime`, rather than casting to correct data types. — Matt Johnson-Pint, Nov 05 '18 at 16:07
Yes it is working but not quite right even though my Session["suspensiondate"] is till December the client can still login which should not be what the system will do instead show the labels I declared — Lance Ebacuado, Nov 05 '18 at 16:13
@LanceEbacuado - You should pass in the date and do the comparison in the query instead of in c#. That is one less step. If you do want to do it in c# then use the appropriate types, do not convert the DateTime to a string and then convert it back again. — Igor, Nov 05 '18 at 16:17
@LanceEbacuado there is a typo in the if condition: `Session["supensiondate"]` should be `Session["suspensiondate"]`, although I don't know if the code has this typo too. — haldo, Nov 05 '18 at 16:18
Also there is no need for the join (2nd table) in your query. — Igor, Nov 05 '18 at 16:19

score 2 · Answer 1 · answered Nov 05 '18 at 16:25

You should pass in the date and do the comparison in the query instead of in c#. That is one less step. If you do want to do it in c# then use the appropriate types, do not convert the DateTime to a string and then convert it back again.
There is no need for the join (2nd table) in your query
You do not have to use a DataReader for this, you can use ExecuteScalar which returns 1 value instead.
Use Add so you can specify the correct schema types with SqlDbType and not AddWithValue

string timestamp = @"SELECT 1 FROM SuspensionRecord sr WHERE sr.User_ID = @User_ID AND supensiondate > @now";
using (SqlCommand cmd2 = new SqlCommand(timestamp, con))
{
    cmd2.Parameters.Add("@User_ID", SqlDbType.Int).Value = Session["UserID"]; // do not convert to string
    cmd2.Parameters.Add("@now", SqlDbType.DateTime).Value = DateTime.Now.Date;
    var result = cmd2.ExecuteScalar();
    if(result != null) // if null then there were no records so account is not suspended
    {
        lblMessage.Text = "The account's status is suspended.";
        lblMessage.Visible = true;
    }
}

And you got one for (hopefully) knowing where this quot is from :-) — Zohar Peled, Nov 05 '18 at 16:33

Zohar Peled · Answer 2 · 2018-11-05T16:32:31.580

First, your SQL is terrible.
You are returning way too much data, and you are using an implicit join (when explicit joins are a part of ANSI-SQL for almost 30 years now!)

Second, Can we stop using AddWithValue() already?

Instead of all this code you can do the entire test on SQL and return a single value:

string sql = 
@"SELECT CASE WHEN EXISTS
  (
      SELECT 1 
      FROM SuspensionRecord 
      WHERE User_ID = @User_ID 
      AND End_Date_Suspension >= CAST(GETDATE() AS DATE)
  ) THEN 1 ELSE 0 END";

Then you can use ExecuteScalar instead of ExecuteReader, and you don't need to loop through all the irrelevant data:

using (SqlCommand cmd2 = new SqlCommand(timestamp, con))
{
    cmd2.Parameters.Add("@User_ID", SqlDbType.Int).Value = Session["UserID"];
    if ((int)cmd2.ExecuteScalar() == 1)
    {
        lblMessage.Text = "The account's status is suspended.";
        lblMessage.Visible = true;
    }
}

Comparing SQL Server DATETIME with DateTime.NOW in C# / ASP.NET

2 Answers2