CODEWITHSUNDEEP
c#asp.netsqlsql-server
Ravi Kant Singh
Ravi Kant Singh

Reputation: 175

How To Set Time Interval using SQL server

I am trying to make the report in which the time interval is minutes and i have the start time & end time and want to have the time interval between two times.

For example.

Start Time:- 8:00PM END TIME:- 16:00PM

Table Structure may be like this.

S.no ---------------------------Time Interval

1--------------------------------8:00 AM - 8:10 AM(ENTRY STARTS)

2--------------------------------8:10 AM- 8:20 AM

3------------------------------- 8:20 AM - 8:30 AM


Nth-----------------------------3:50PM - 4:00 PM(LAST ENTRY FOR
 REPORT)

How can i do this using on SQL query and i want to use it in Asp.Net GridView?

For example.

RE TABLE STRUCTURE

using the below data now iam trying to put the no of keys between the start time of workdatetime and last of this workdatetime/

Logic is SAme as PREVIOUS but unable to put the NO OF KEYS between the Time Interval. I wan to show the structure same as above only add one column No of keys with respect to the time interval/

WorkdateTime **** NoOfKeys

16:52:04 **** 54

16:52:06 **** 0

16:52:07 **** 2

16:52:25 **** 0

16:52:26 **** 0

16:52:35 **** 0

16:52:35 **** 0

16:53:15 **** 0

16:53:55 **** 0

16:54:28 **** 11

16:54:35 **** 0

16:55:15 **** 0

16:55:55 **** 0

16:56:35 **** 0

16:57:15 **** 0

16:57:28 **** 103

16:57:55 **** 0

16:58:35 **** 0

16:58:39 **** 2

16:59:09 **** 19

16:59:15 **** 0

16:59:42 **** 40

16:59:43 **** 2

16:59:55 **** 0

17:00:35 **** 0

17:01:15 **** 0

17:01:35 **** 4

17:01:55 **** 0

17:02:35 **** 0

17:03:15 **** 0

17:03:55 **** 0

17:03:58 **** 2

17:04:24 **** 3

17:04:35 ****

17:05:15 **** 0

17:05:40 **** 0

17:05:46 **** 14

17:05:47 **** 0

17:05:50 **** 2

17:06:30 **** 0

17:06:37 **** 1

17:07:10 **** 0

17:07:50 **** 0

17:08:12 **** 1

17:08:30 **** 0

17:09:10 **** 0

17:09:27 **** 42

17:09:50 **** 0

Upvotes: 1

Views: 4506

Answers (3)

Dipendu Paul
Dipendu Paul

Reputation: 2753

Here is a solution using CTE

DECLARE @StartDate DATETIME, @EndTime DATETIME

SELECT @StartDate = '8:00 PM'
SELECT @EndTime = '9:00 PM'


;WITH time_cte(StartTime, EndTime) AS
(
   SELECT @StartDate StartTime, DATEADD(mi, 10, @StartDate) EndTime

   UNION ALL

   SELECT EndTime, DATEADD(mi, 10, EndTime)  FROM time_cte
   WHERE EndTime < @EndTime

)  
SELECT * FROM time_cte

Check its working at SQL Fiddle

Upvotes: 0

Habeeb
Habeeb

Reputation: 1040

declare @Start time
declare @end time
declare @request int

set @Start = '08:00:00'
set @end = '16:00:00'
set @request = 1

;with Dates as (
    select @request as reqId,@Start as reqDate
    union all
    select reqId+1,DATEADD(MINUTE,10,reqDate) from Dates
    where reqDate < @end
)
select reqId,convert(varchar(8),reqDate,100)+'-'+convert(varchar(8),DATEADD(MINUTE,10,reqDate),100) "Time Interval" from Dates

Use this for ur reports

Upvotes: 2

schei1
schei1

Reputation: 2487

Take a look at DateDiff

SELECT DATEDIFF(second, '2000-01-01 08:00:00' , '2000-01-01 16:00:00');

Return the difference/interval in seconds. Divide by 60 to get minutes, and divide by 60 again to get hours.

Since you are only using the time component, you can set the datepart to whatever you like.

EDIT:

Since I misread the question, maybe you want something like this:

DateTime startAt = new DateTime(2000, 01, 01, 8, 0, 0);
DateTime endAt = new DateTime(2000, 01, 01, 16, 0, 0);

for (DateTime date = startAt; date < endAt; date = date.AddMinutes(10))
{
    // create something with this interval
    Console.WriteLine(string.Format("Interval start: {0}, Interval End: {1}", date.ToString("HH:mm"), date.AddMinutes(10).ToString("HH:mm")));
}

Just use the start date and end date and loop through it with 10min intervals.

Upvotes: 1

Related Questions