Round Time To Nearest 15 Miniutes

I wrote a function to round time to the nearest 15 miniute interval.
This functions works fine, I'm just wondering if anyone has a more
efficient\better method of doing this.
CREATE FUNCTION RoundToNearest15
(
@.Date SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @.Miniute TINYINT
DECLARE @.Mult SMALLINT
DECLARE @.Value TINYINT
SET @.Miniute = DATEPART(MI,@.Date)
SET @.Value = 0
SET @.Mult = 1
IF @.Miniute < 8
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute
END
IF @.Miniute BETWEEN 16 AND 22
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 15
END
IF @.Miniute BETWEEN 31 AND 37
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 30
END
IF @.Miniute BETWEEN 46 AND 52
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 45
END
IF @.Miniute BETWEEN 8 AND 14
SET @.Value = 15 - @.Miniute
IF @.Miniute BETWEEN 23 AND 29
SET @.Value = 30 - @.Miniute
IF @.Miniute BETWEEN 38 AND 44
SET @.Value = 45 - @.Miniute
IF @.Miniute BETWEEN 53 AND 59
SET @.Value = 60 - @.Miniute
RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
ENDOn Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
> I wrote a function to round time to the nearest 15 miniute interval.
> This functions works fine, I'm just wondering if anyone has a more
> efficient\better method of doing this.
> CREATE FUNCTION RoundToNearest15
> (
> @.Date SMALLDATETIME
> )
> RETURNS SMALLDATETIME
> AS
> BEGIN
> DECLARE @.Miniute TINYINT
> DECLARE @.Mult SMALLINT
> DECLARE @.Value TINYINT
> SET @.Miniute = DATEPART(MI,@.Date)
> SET @.Value = 0
> SET @.Mult = 1
> IF @.Miniute < 8
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute
> END
> IF @.Miniute BETWEEN 16 AND 22
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 15
> END
> IF @.Miniute BETWEEN 31 AND 37
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 30
> END
> IF @.Miniute BETWEEN 46 AND 52
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 45
> END
> IF @.Miniute BETWEEN 8 AND 14
> SET @.Value = 15 - @.Miniute
> IF @.Miniute BETWEEN 23 AND 29
> SET @.Value = 30 - @.Miniute
> IF @.Miniute BETWEEN 38 AND 44
> SET @.Value = 45 - @.Miniute
> IF @.Miniute BETWEEN 53 AND 59
> SET @.Value = 60 - @.Miniute
> RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
> END
How about this:
SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
0)|||On Feb 21, 10:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
> How about this:
> SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
> 0)- Hide quoted text -
> - Show quoted text -
Excellent, I knew there had to be an easier way. Thanks!|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172074684.107644.231970@.q2g2000cwa.googlegroups.com...
> On Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
> How about this:
> SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
> 0)
I tried this on 7:07:45 and it incorrectly returned 7:00 not 7:15.
PS|||On Feb 21, 4:41 pm, "PS" <ecneserpeg...@.hotmail.com> wrote:
> "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote in message
> news:1172074684.107644.231970@.q2g2000cwa.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
> I tried this on 7:07:45 and it incorrectly returned 7:00 not 7:15.
> PS
That's because the seconds part of the time value is ignored, I'm only
working with full minutes. You could do something similar with
seconds, but you'll need to DATEDIFF against something other than "0"
or you'll get an overflow error.