Next half an hour time - 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

Next half an hour time

Author

Topic

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-16 : 04:20:42

Hi,
Explmple:

2011-05-16 14:06:24.747 to 2011-05-16 14:30:00.000

2011-05-16 13:45:24.747 to 2011-05-16 14:00:00.000

2011-05-16 13:06:24.747 to 2011-05-16 13:30:00.000

here is the sample table

create table #temp
(
startdate datetime
,enddate datetime
)

insert into #temp
select '2011-05-16 13:35:54.930','2011-05-16 13:00:54.930' union all
select '2011-05-16 22:45:54.930','2011-05-16 13:5:54.930' union all
select '2011-05-16 13:58:54.930','2011-05-16 13:15:54.930' union all
select '2011-05-16 13:30:54.930','2011-05-16 13:29:54.930'

select * from #temp
-- Expected output:
I need to convert field to next half an hour
(ex: 12:35:00.000 to 13:00:00.000 ond 12:12:00.000 to 12:30:00.0000)

-- Expected output:
startdate enddate
2011-05-16 14:00:00.000 2011-05-16 13:30:00.000
2011-05-16 23:00:00.000 2011-05-16 13:30:00.000
2011-05-16 14:00:00.000 2011-05-16 13:30:00.000
2011-05-16 14:00:00.000 2011-05-16 13:30:00.000

Please help me

--Ranjit

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-16 : 04:55:10

Sorry ,

I got it by using following query.

select
dateadd(minute,30,dateadd(minute,(datediff(minute,0,startdate)/30)*30,0))startdate
,dateadd(minute,30,dateadd(minute,(datediff(minute,0,enddate)/30)*30,0))enddate
from #temp

--Ranjit

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-16 : 05:29:06

Simplify...

SELECT	StartDate,
	DATEADD(MINUTE, DATEDIFF(MINUTE, '00:00:00', StartDate) / 30 * 30, '00:30:00'),
	EndDate,
	DATEADD(MINUTE, DATEDIFF(MINUTE, '00:00:00', EndDate) / 30 * 30, '00:30:00')
FROM	#Temp

N 56°04'39.26"
E 12°55'05.63"

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-05-16 : 06:01:38

Thanks Peso,

I'll use your code

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources