CODEWITHSUNDEEP
c#asp.net
JeT_BluE
JeT_BluE

Reputation: 33

Compare system date with a date field in SQL

I am trying to compare a date record in SQL Server with the system date. In my example the user first register with his name and date of birth which are then stored in the database. The user than logs into the web application using his name only. After logging in, his name is shown on the side where it says "Welcome "player name" using Sessions.

What I am trying to show in addition to his name is a message saying "happy birthday" if his date of birth matches the system date. I have tried working with System.DateTime.Now, but what I think is that it is also comparing the year, and what I really want is the day and the month only. I would really appreciate any suggestion or help.

CODE In Login page:

protected void Button1_Click(object sender, EventArgs e)
{
  String name = TextBox1.Text;
  String date = System.DateTime.Today.ToShortDateString();
  SqlConnection myconn2 = new 
    SqlConnection(ConfigurationManager.ConnectionStrings["User"].ToString()); 

  SqlCommand cmd2 = new SqlCommand();
  SqlDataReader reader;

  myconn2.Open();
  cmd2 = new SqlCommand("Select D_O_B from User WHERE Username = @username", 
                        myconn2);
  cmd2.Parameters.Add("@username", SqlDbType.NVarChar).Value = name;
  cmd2.Connection = myconn2                           
  cmd2.ExecuteNonQuery();
  reader = cmd2.ExecuteReader();
  while (reader.Read().ToString() == date)
  {
    Session["Birthday"] = "Happy Birthday";
  }
}

Note: I using the same reader in the code above this one, but the reader here is with a different connection. Also, reader.Read() is different than reader.HasRows?

Code in Web app Page:

string date = (string)(Session["Birthday"]); // Retrieving the session
Label6.Text = date;

Upvotes: 0

Views: 1862

Answers (3)

Ňuf
Ňuf

Reputation: 6217

Use ExecuteScalar instead of ExecuteNonQuery() and ExecuteReader(). If D_O_B column in your database is datetime, you can just cast result to DateTime. If D_O_B column is varchar (or something similar), you have to use DateTime.Parse() to convert string to DateTime. Then just compare Day and Month parts of DateTime instances:

DateTime DOB = (DateTime)cmd2.ExecuteScalar();
DateTime Today = DateTime.Now;
if (Today.Month == DOB.Month && Today.Day == DOB.Day)
{
    //Happy Birthday
}

Upvotes: 1

Toan Nguyen
Toan Nguyen

Reputation: 11601

You can replace the code fragment to compare date above with this one

object dobVal = null;
while ((dobVal= reader.Read()) != null)
{
  var storedDob = Convert.ToDateTime(dobVal.ToString());

  if(storedDob.Month == DateTime.Now.Month && 
     storedDob.Day == DateTime.Now.Day)
  {
    Session["Birthday"] = "Happy Birthday";
  }
}

Upvotes: 1

gh9
gh9

Reputation: 10703

Two ways, within in sql you can do a date compare between months and days using the attached page as a reference

sql date compare (bad way for what you want to do)

2) you can cast the incoming string to date time and do a system.datetime.month and system.datetime.day compare against the casted datetime from sql c# better way

I would chose to do way number 2

Upvotes: 0

Related Questions