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.
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.0002011-05-16 13:45:24.747 to 2011-05-16 14:00:00.0002011-05-16 13:06:24.747 to 2011-05-16 13:30:00.000here is the sample tablecreate table #temp(startdate datetime,enddate datetime)insert into #tempselect '2011-05-16 13:35:54.930','2011-05-16 13:00:54.930' union allselect '2011-05-16 22:45:54.930','2011-05-16 13:5:54.930' union allselect '2011-05-16 13:58:54.930','2011-05-16 13:15:54.930' union allselect '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 enddate2011-05-16 14:00:00.000 2011-05-16 13:30:00.0002011-05-16 23:00:00.000 2011-05-16 13:30:00.0002011-05-16 14:00:00.000 2011-05-16 13:30:00.0002011-05-16 14:00:00.000 2011-05-16 13:30:00.000Please 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))enddatefrom #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 |
|
|
|
|
|
|
|