Split time on 15 minute interval

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split time on 15 minute interval

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-16 : 08:17:54

Hi Friends,

I have to parameter as
time1 - 10:43 and time2 -12:30,
time1 - 14:20 and time2 -23:30,

I need the output on 15 minute interval like:

10:43
11:00
11:15
11:30
11:45
12:00
12:15
12:30
14:20
14:30
and upto 23:30

How to do this. Please ASAP.

Thanks,
Pushkar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-16 : 08:37:24

this is not 15 mins interval.

10:43
11:00

Please explain your logic

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-16 : 08:40:27

What is the logic for skipping 10:45, but not skipping 14:30?

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-16 : 09:12:43

U r right sunita, 10:45 should not have been skiped. like if this is 12:09 then second return would be 12:15
12:09
12:15
12:30
and then 15 minute interval

Thanks,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-16 : 10:04:36

How are you given the parameters? In a table, or as variables? Here is an example of how you can write a query, assuming you have variables that define the start time and end time.

DECLARE @startTime TIME, @endTime TIME;
SET @startTime = '10:43';
SET @endTime = '12:30';

;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE N < 50)
SELECT @startTime AS T 
UNION ALL
SELECT
	CAST(DATEADD(minute,15*n+DATEDIFF(minute,0,@startTime)/15*15,0) AS TIME)
FROM
	N
WHERE
	CAST(DATEADD(minute,15*n+DATEDIFF(minute,0,@startTime)/15*15,0) AS TIME) <= @endTime;

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-17 : 02:44:00

You are awesome and that worked for me thanks, but I have another issue that in case if time passed
Midnights 24:00 then I have to continue like if end time is 02:15 AM and start time is 10:00PM then start from 10:00 and split up to 02:15.

I'm supplying parameter from table

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-05-17 : 23:56:13

IF (cast(@TIMEFROM as int)>cast(@TIMETO as int)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 00:19:20

[code]
DECLARE @startTime datetime = '22:45',
@endTime datetime = '03:31'

if @endTime < @startTime
begin
select @endTime = dateadd(day, 1, @endTime)
end

; with rcte as
(
select [time] = dateadd(minute, ceiling(datediff(minute, 0, @startTime) / 15.0) * 15, 0)

union all

select [time] = dateadd(minute, 15, [time])
from rcte
where dateadd(minute, 15, [time]) <= @endTime
)
select [time] = @startTime
union
select *
from rcte
union
select [time] = @endTime
order by [time]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-18 : 01:43:15

thanks khtan, can we show date with time actually current date and change date after 12:00 PM? How?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 02:37:24

quote:
Originally posted by pushp82

thanks khtan, can we show date with time actually current date and change date after 12:00 PM? How?

Don't really get what you mean here .. Can you elaborate further or perhaps with some sample data ?

KH
[spoiler]Time is always against us[/spoiler]

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-18 : 05:47:13

Hi khtan ,

I'm almost done with but the issue left is I have a column contain values as
00.45
00.30
01.15
01.20
00.20

as decimal (18,2)

I need to chnage this time to minutes(MM) and then return the values greater than that value so the issue left is convert these values to minute
can u help me ASAP
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-18 : 06:34:00

[code]
declare @d decimal(18,2)

select @d = 01.15

select @d, dateadd(minute, @d * 100 % 100, dateadd(hour, floor(@d), 0))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources