How to compare system date with date existing in database table

Question

I have 2 columns in my table TimeTable

start_date (format is DD-MM-YYYY)
end_date

If I log in from my login page, I want to check that current date is between start_date and end_date; if so, I am able to log in.

How to do comparison in C#..asp.net..?

I have printed current date in login page successfully.

lblMsg.Text= DateTime.Now.ToString("dd-MM-yyyy");

I am using asp.net with c#

      start_date              end_date
8/2/2013 12:00:00 AM    10/2/2013 12:00:00 AM

Code for Storing Date in DD/MM/YYYY format

CultureInfo provider = CultureInfo.InvariantCulture;

        DateTime startdate = DateTime.ParseExact(tbstartdate.Text.ToString().Trim(), "dd/MM/yyyy", provider);
        DateTime enddate = DateTime.ParseExact(tbenddate.Text.ToString().Trim(),"dd/MM/yyyy",provider);
        string sql = "INSERT INTO TimeTable(start_date,end_date)"
     + "VALUES (@startdate,@enddate)";
        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        SqlCommand cmd = new SqlCommand(sql, conn);

        cmd.Parameters.Add("@startdate", SqlDbType.DateTime).Value = startdate;
        cmd.Parameters.Add("@enddate", SqlDbType.DateTime).Value = enddate;

        cmd.Prepare();
        n = cmd.ExecuteNonQuery();

        conn.Close();

Code for Checking Date on login page

CultureInfo provider = CultureInfo.InvariantCulture;

        DateTime date = DateTime.ParseExact(DateTime.Now.ToString("dd/mm/yyyy"), "dd/MM/yyyy", provider);
      //  DateTime date=DateTime.Now.ToString("dd/mm/yyyy");

        string query1 = "select * from TimeTable where ((start_date >= @date1 and End_date<= @date2) and UserType=@type)";
        SqlConnection conn = new SqlConnection(ConnectionString);
        conn.Open();
        SqlCommand com = new SqlCommand(query1, conn);
        com.Parameters.Add("@type",dpusertype.SelectedItem.Text.ToString());
        com.Parameters.Add("@date1",date);
        com.Parameters.Add("@date2", date);
        com.CommandType = CommandType.Text;
        SqlDataAdapter da = new SqlDataAdapter(com);
        DataTable dt = new DataTable();
        da.Fill(dt);

`if (dt.Rows.Count > 0) // comparing users from table

        {

            FormsAuthentication.RedirectFromLoginPage(txtFcode.Text, Persist.Checked);
            Response.Redirect("~/DeptCoordinator/DeptCoordinator_homepage.aspx");  
        }

Getting Error

The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar. Desc`

http://stackoverflow.com/questions/2821040/how-do-i-find-the-time-difference-between-two-datetime-objects-in-asp-net. — Dawood Ahmed, Feb 08 '13 at 04:49
Well you can have the check as a database query, so something like `GetDate()` or `sysdate` should work for you but you need to provide additional info for these relevant things — V4Vendetta, Feb 08 '13 at 04:55
What **datatype** are your `start_date` and `end_date` columns? — marc_s, Feb 08 '13 at 05:49

score 3 · Answer 1 · answered Feb 08 '13 at 04:29

3

Assuming that you've loaded start_date and end_from from the table. Then

var now = DateTime.Now.Date;
var allowLogin = start_date <= now && now <= end_date;

answered Feb 08 '13 at 04:29

Richard Schneider

34,944
9
57
73

@marc_s No it doesn't. The code that loads the variables can do any coercion that is requjired. – Richard Schneider Feb 08 '13 at 05:52

score 0 · Answer 2 · edited May 23 '17 at 11:59

DateTime.Ticks will account for the time.
Use .Ticks on the DateTime to convert your dates into longs.
Then just use a simple if stmt to see if your target date falls between.

var now = DateTime.Now.Date;

 // Assuming you know d2 > d1
if (now .Ticks > start_date.Ticks && now.Ticks < end_date.Ticks)
{
    // targetDt is in between d1 and d2
}

Edit 1

But I will suggest you to compare it in the database rather than c#.

   select 
        columnName1, 
        columnName2,
        ....
   from 
       tableName
   where
       GETDATE() between start_date and end_date;

Some useful links:-
Selecting between two dates within a DateTime field - SQL Server
Check if datetime instance falls in between other two datetime objects