C# date and time comparing

Question

I have a table which contains a two column(start_time and end_time).I am getting the information of start and end time from the user and adding it to the table.Once the user enters the next start and end time I have to compare it with the database.

Suppose in table one row has start time as 2011-08-10 16:00:00 and end time is 2011-08-10 16:30:00. Suppose the user enter value 2011-08-10 16:05:00.000 (start_time) and 2011-08-10 16:25:00 (end_time) I am able to capture the by using

      String getConflictTimeInBetween = string.Format("select                           question_id,question_text from " + data_variables.RES_TXT_STRING_QUESTION_TABLE + " where start_time<='{0}' and end_time>='{1}'", start_full, end_full);//question_text='DFS'"2011-06-23 14:55);//
                        com = new SqlCommand(getConflictTimeInBetween, myConnection);
                        dr = com.ExecuteReader();

                        if (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                //Assign to your textbox here   
                                conflictQuestionIdAtBetween = dr["question_id"].ToString();
                                conflictQuestionTextAtBetween=dr["question_text"].ToString();
                            }
                        }

Here are some sample overlaps that I want to prevent

start_time from 2011-08-10 15:55:00 and end_time 2011-08-10 16:05:00 (five minutes overlap with already existing data)
start_time from 2011-08-10 16:25:00 and end_time 2011-08-10 17:00:00 (five minutes overlap with already existing data)
start_time from 2011-08-10 15:00:00 and end_time 2011-08-10 17:00:00 (30 minutes overlap with already existing data)

Can anyone help me how to solve these three issues.

This is trivial. Please provide us with what you have tried and explain why it doesn't work. — Daniel Hilgarth
– Daniel Hilgarth, Commented Aug 10, 2011 at 11:30
I think you're using the wrong term. Do you mean you want to prevent duplicate/overlapping data that happens in the same time range? — user447356
– user447356, Commented Aug 10, 2011 at 11:47
do you want to see overlap? do you want to get only times in between start and end? (in which case you will eventually have a timeframe of only 1 minute) exactly what is it you want to achieve? — mtijn
– mtijn, Commented Aug 10, 2011 at 11:47
I don't want any overlapping to be have in between the start time and end time — bharathi
– bharathi, Commented Aug 10, 2011 at 11:53

mtijn · Accepted Answer · 2011-08-10 11:55:44Z

1

None of the 3 overlapping scenarios you mentioned will show up with the query you're using now. It's not clear from your post what you mean to achieve, but I can give you the queries that will show each scenario:

1) "select question_id,question_text from " + data_variables.RES_TXT_STRING_QUESTION_TABLE + " where start_time>'{0}' and start_time<'{1}'", start_full, end_full);//question_text='DFS'"2011-06-23 14:55);

2) "select question_id,question_text from " + data_variables.RES_TXT_STRING_QUESTION_TABLE + " where end_time>'{0}' and end_time<'{1}'", start_full, end_full);//question_text='DFS'"2011-06-23 14:55);

3) "select question_id,question_text from " + data_variables.RES_TXT_STRING_QUESTION_TABLE + " where start_time>'{0}' and end_time<'{1}'", start_full, end_full);//question_text='DFS'"2011-06-23 14:55);

answered Aug 10, 2011 at 11:55

mtijn

3,6762 gold badges35 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bharathi Over a year ago

I know the above which i provided is not for these 3 issues.But how to handle these 3 issues.the 3 query u provided is all same.How to capture it.

MrFox · Accepted Answer · 2011-08-10 13:59:22Z

0

Since you seem to have the SQL part, here's the algorithm that finds the overlap in ticks between the input time and the row time.

    public long GetTimeOverlap(long inputStart, long inputEnd)
    {
        // I assume you can get the data yourself so heres only the algorithm.
        long rowStart = new DateTime().Ticks, rowEnd = new DateTime().Ticks;

        if (inputStart < rowStart)
            if (inputEnd >= rowEnd)
                // case 3
                return rowEnd - rowStart;
            else if (inputEnd > rowStart)
                // case 1
                return inputEnd - rowStart;
            // Input time is before row time.
            else return 0;
        else if (inputStart >= rowEnd)
            // Input time is after row time.
            return 0;
        else if (inputEnd >= rowEnd)
            // case 2
            return rowEnd - inputStart;
            // case 0
        else return inputEnd - inputStart;
    }

answered Aug 10, 2011 at 13:59

MrFox

5,1849 gold badges48 silver badges83 bronze badges

1 Comment

mtijn Over a year ago

This will work if combined with my 3 queries and his query, but then he'd be getting much data back from the database and do the calculating on the client side. If I were him I'd use a better query to let the database do the math. Nonetheless, this is still an option.

user447356 · Accepted Answer · 2011-08-10 11:52:24Z

Not sure what you mean in your question, however here is much better code:

String getConflictTimeInBetween = string.Format("select question_id,question_text from {0}  where start_time<=@start and end_time>=@end", data_variables.RES_TXT_STRING_QUESTION_TABLE);
using (com = new SqlCommand(getConflictTimeInBetween, myConnection))
{
    com.Parameters.AddWithValue("@start", Convert.ToDateTime(start_full));
    com.Parameters.AddWithValue("@end", Convert.ToDateTime(end_full));
    using (dr = com.ExecuteReader())
    {
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                //Assign to your textbox here   
                conflictQuestionIdAtBetween = dr["question_id"].ToString();
                conflictQuestionTextAtBetween=dr["question_text"].ToString();
            }
        }
    }
}

It's doing the same thing plus:

Prevent possible SQL Injection attacks by using Parameters instead of directly injecting the text.
Dispose the objects (command and reader) after using them to prevent connections from remaining open and crashing your database. This is done by the using blocks.

Giuseppe Romagnuolo · Accepted Answer · 2011-08-10 12:16:20Z

0

I believe what you want to do to intersect the date ranges correctly is something like:

   String getConflictTimeInBetween = string.Format("select question_id,question_text from " + data_variables.RES_TXT_STRING_QUESTION_TABLE + "where (start_time<='{0}' and end_time>='{0}') or ((start_time<='{1}' and end_time>='{1}')", start_full, end_full);

edited Aug 10, 2011 at 12:16

answered Aug 10, 2011 at 11:49

Giuseppe Romagnuolo

3,4122 gold badges32 silver badges38 bronze badges

Collectives™ on Stack Overflow

C# date and time comparing

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related