CODEWITHSUNDEEP
c#sql
bharathi
bharathi

Reputation: 6271

C# date and time comparing

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

  1. 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)

  2. 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)

  3. 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.

Upvotes: 0

Views: 236

Answers (4)

MrFox
MrFox

Reputation: 5106

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

Upvotes: 0

Giuseppe Romagnuolo
Giuseppe Romagnuolo

Reputation: 3402

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);

Upvotes: 0

mtijn
mtijn

Reputation: 3678

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);

Upvotes: 1

Shadow Wizard
Shadow Wizard

Reputation: 66399

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:

  1. Prevent possible SQL Injection attacks by using Parameters instead of directly injecting the text.
  2. 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.

Upvotes: 0

Related Questions