30 min duration - SQL Server Forums

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

30 min duration

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-11 : 06:43:35

create table #temp
(
id int identity(1,1)
,startdate datetime
)

insert into #temp(startdate)
select '2011-05-11 16:06:53.323' union all
select '2011-05-16 23:06:53.323' union all
select '2011-05-12 03:06:53.323' union all
select '2011-05-17 01:06:53.323'

expected output:
ID startdateduration
1 2011-05-11 00:00
1 2011-05-11 00:30
. .
. .
. .
1 2011-05-11 23.30
2 2011-05-16 00:00
2 2011-05-16 00:30
. .
. .
. .
2 2011-05-16 23.30

like wise for all Id's i need to get 00:00 to 23.30 irresepctive of startdate time

Thanks in advance...

--Ranjit

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 07:29:31

Do you want the nearest 30 minute interval to each startdate, or just an output of 30 minute intervals?

Jim

Everyday I learn something that somebody else already knew

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-11 : 07:42:46

Hi jimf ,Thanks for u r reply...

it just output of 30 min intervals..

I did using cte


;with cte as
(
select  
	id
   ,convert(datetime,CONVERT(VARCHAR(15), startdate, 112)) as datestart
from #temp 

union all

select 
	c.id
	,c.datestart+'00:30:00' as day1
from cte c 
join #temp r on r.id=c.id
where dateadd(minute,1410,convert(datetime,CONVERT(VARCHAR(15), r.startdate, 112)))>c.datestart  
)
select * from cte
order by id

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources