Datetime Round to Half Hour 1

[Moebius01]

After tinkering around a bit, (searching yielded no results) I came up with this to round the time to the current half hour:

DateAdd(mi,DateDiff(mi,0,DateAdd(mi,-(DatePart(mi, GetDate())), GetDate())),0)

Returns 04/04/2005 10:00:00.000 at 10:25 am this morning for example.

As far as I can tell, this works, but in the quest for cleanness, I was wondering if 1) I had any redundant parts in there and 2) if there's an easier way to do this?

 

[Moebius01]

To follow up on my own post, I just realized this turns out to be a convoluted way to round down to the hour rather than half hour.

Which leads me back to question 2. Is there a way to get the current half hour? ie.. 10:25 am will return 10:00 and 10:37 will return 10:30.

 

[vongrunt]

Try this:

select dateadd(mi, 30 * (datediff(mi, 0, getdate()) /30), 0)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Moebius01]

I knew there had to be an easy way to find it, but I started having the proverbial trees block my view of the forrest.

On a quick followup, to get the previous half hour, would the easiest way be to encapsulate what you have into a -30 datediff?

 

[vongrunt]

Probably. Another way that just crossed my mind is:

select dateadd(mi, 30 * (datediff(mi, 0, getdate()) /30 [b]- 1[/b]), 0)

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba