how to check time values in database

Question

User-900352205 posted

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

Answer 1

User3866881 posted

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!");
            }

check for days if different no clash

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。

 

Answer 2

User-900352205 posted

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

Answer 3

User3866881 posted

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。

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

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……
}