Next half an hour time

Question

Post reply

Next half an hour time

Nagaram

SSCrazy

Points: 2110
More actions
May 16, 2011 at 2:26 am

#250322

Hi,
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:
startdateenddate
2011-05-16 14:00:00.0002011-05-16 13:30:00.000
2011-05-16 23:00:00.0002011-05-16 13:30:00.000
2011-05-16 14:00:00.0002011-05-16 13:30:00.000
2011-05-16 14:00:00.0002011-05-16 13:30:00.000
Please help me
--Ranjit

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nagaram SSCrazy Points: 2110 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Thanks for posting the feedback... will help someone else someday.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

As a sidebar and I don't know if your data is actual data or not, but it's interesting that the start dates in the example date are later than the end dates. If that data came from something real, you may have another problem to consider.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Nagaram SSCrazy Points: 2110 More actions · Answer 4

I Blindly inserted data for sample

my intension was to display next half an hour

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

See http://facility9.com/2010/02/24/rounding-to-the-nearest-x-minutes for a way of doing this through a reference table as an alternative. Note that Jeremiah is rounding differently than I would when he builds the table, where I'd never be assigning something to the "60" interval, but his choice. (See my smartass comment toward the bottom of the comments section there about 08:60 am)

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."