how to check time values in database RSS

• • Reply
  

  speed1

  Member

  5 Points

  5 Posts

  how to check time values in database

  Nov 29, 2011 07:32 PM|LINK

  Hi
  I'm working on a project that require to check for times of university courses so there is no clash between courses

  this is sample of database.

  the constraint of these courses is "Morning" >>> this mean that End_time must be <= 12:00pm
  now how to access database and check if:
  1- End_time <= 12:00pm (it's match the constraint)
  2- check for days if different no clash
  3- if same days > check for time (courseB Start_time must be >= courseA End_time

  * I need to emplement a button to do all these checking using C#

  course name	Start_time	End_time	day
  A	8:30	10:30	sun
  B	9:30	10:30	sun

  • Edited by speed1 on Nov 30, 2011 08:50 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: how to check time values in database

  Dec 01, 2011 01:53 AM|LINK

  speed1

  1- End_time <= 12:00pm (it's match the constraint)
  

  Hello：）

  For the first checking，I think you can read the StartTime and EndTime to check whether the time is smaller than 12:00——Here's my sample for checking：

   //Suppose this is the read-out values：
              string s = "13:00";
   
              //Convert to timespan
              TimeSpan t = TimeSpan.Parse(s);
   
              if (t <= TimeSpan.Parse("12:00"))
              {
                  Console.WriteLine("OK");
              }
              else
              {
                  Console.WriteLine("Time is overdue!");
              }

  speed1

  check for days if different no clash
  

  speed1

  3- if same days > check for time (courseB Start_time must be >= courseA End_time

  In fact you can do this：

  Select count(*) from xxx where StartTime=XXX and EndTime=YYY and day=zzz

  If the result >1，this means you must be conflicted。You should either adjust the "day" or the startime。

   

  • Edited by decker dong - msft on Dec 05, 2011 01:12 AM
  • Edited by decker dong - msft on Dec 05, 2011 01:12 AM
  • Marked as answer by speed1 on Dec 19, 2011 05:13 AM
• • Reply
  

  speed1

  Member

  5 Points

  5 Posts

  Re: how to check time values in database

  Dec 03, 2011 07:01 AM|LINK

  
  Hi
  Do I have to use "using System.Data.SqlClient;" and how to access the data base and read values
  I have to access database and read value of time such as Start_time1 = 8:00am End_time1 = 10:00am
  and then compare another start time with it, so if Start_time2 = 9:00 it must detect it
  
  

• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: how to check time values in database

  Dec 03, 2011 11:48 PM|LINK

  speed1

  Do I have to use "using System.Data.SqlClient;"

  Yes，It's a must if you want to use it to read out data contents from db。

  speed1

  how to access the data base and read values

  Just use SqlDataAdapter and Fill method to read into DataTable，and do comparation。For more about this usage，please see：

  http://msdn.microsoft.com/en-us/library/905keexk.aspx

  speed1

  have to access database and read value of time such as Start_time1 = 8:00am End_time1 = 10:00am
  and then compare another start time with it, so if Start_time2 = 9:00 it must detect it
  

  Well，I think you should change your column's type as DateTime by using its constructor to assign year，month，day，hour，minute and seconds。Then do a cast by using DateTime.Parse(Your DateTime) and use its property called "TimeOfDay" to do comparation within a foreach loop：

  Sample codes similar like this：

  foreach(DataRow row in DataTable.Rows)
  {
      TimeSpan ts = DateTime.Parse(row["TimeColumn"].ToString()).TimeOfDay;
      //Here do comparation……
  }

  • Marked as answer by decker dong - msft on Dec 05, 2011 01:12 AM