Rounding to the nearest thousand

Hi

Which parameter value for the Round function do I need to pass to get it to round to the nearest thousand ?

Thanks,
Neil

Hello Neil,

Try this:

=Round(Fields!Field1.Value, 3)

Hope this helps.

Jarret

|||

Sorry, I mis-read your question. I thought it said thousandth.

This should do what you want:

=cInt(Fields!Field1.Value / 1000) * 1000

Jarret

|||

For formatting it is

#,###,.

|||Thanks Jarret, Just what I needed..

Thanks!!
|||Thanks Ewild!

You read my mind!! Also just what I needed

Rounding seconds up to the nearest 15 minutes

I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.

Hi,

You need to divide the seconds by 15 first, round that (up or down as your logic dictates) and then divide by 4 to get hours.

Try this as example:

DECLARE @.secondsint
SET @.seconds = 1632
DECLARE @.hoursdecimal(18,2)
SET @.hours = CEILING(convert(decimal,@.seconds)/15 )
SELECT @.hours, @.hours/4

The result should be 109, 27.25 (27 and a quarter hours).
I included the two results so you can see what is happening.
Change the seconds to 1640 and the figures are 110, 27.50 (27 and half hours)

|||

Okay, you totally lost me. I only get to dabble with mssql every few months so please excuse my ignorance. I am trying to work what you said into my query, but I when I put in the decimal it throws an error.

SELECT dbo.SLPTRANS.ClientID,SUM(dbo.SLPTRANS.TransValue)AS Expr1,SUM(CEILING(dbo.SLPTRANS.TimeSpent / 15) * dbo.SLPTRANS.RateValue)AS BillableFeesFROM dbo.SLPTRANSINNERJOIN dbo.INVOICEON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordIDGROUP BY dbo.SLPTRANS.ClientIDHAVING (dbo.SLPTRANS.ClientID = 405)

|||

Hi,
Apologies for the late reply!

I think the SUM should be:
SUM( (CEILING(db.SLPTRANS.TimeSpent /15)/4 ) * dbo.SLPTRANS.RateValue)

What error message do you get?

Rounding

I am trying to round values to the nearest $5.
I work in the casino industry and we offer patrons cashback on thier play.
Let's say someone earns $42 cash back. I need to send them an extra check
for 25% rounded to the nearest $5.
$42 * 25% = 10.5 which I need to round to $10
$53 * 25% = 13.25 which I need to round to $15
Similiar to basic rounding...2.49 rounds to 2 and 2.5 rounds to 3 I need
12.49 to round to 10 and 12.50 to round to 15.
Does anyone know how to mathamatically program SQL to do so? Is there a
function that can help out?
Thanks."Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:eSEbcu1TGHA.4384@.tk2msftngp13.phx.gbl...
>I am trying to round values to the nearest $5.
> I work in the casino industry and we offer patrons cashback on thier play.
> Let's say someone earns $42 cash back. I need to send them an extra check
> for 25% rounded to the nearest $5.
> $42 * 25% = 10.5 which I need to round to $10
> $53 * 25% = 13.25 which I need to round to $15
> Similiar to basic rounding...2.49 rounds to 2 and 2.5 rounds to 3 I need
> 12.49 to round to 10 and 12.50 to round to 15.
> Does anyone know how to mathamatically program SQL to do so? Is there a
> function that can help out?
> Thanks.
declare @.num1 int
declare @.num2 int
set @.num1 = 42
set @.num2 = 53
select cast((@.num1 * .25)/5 as decimal(5,0)) * 5
select cast((@.num2 * .25)/5 as decimal(5,0)) * 5|||Thanks...I tried multiple scenerios and it worked in all cases.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23e4oa11TGHA.4520@.TK2MSFTNGP10.phx.gbl...
> "Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
> news:eSEbcu1TGHA.4384@.tk2msftngp13.phx.gbl...
> declare @.num1 int
> declare @.num2 int
> set @.num1 = 42
> set @.num2 = 53
> select cast((@.num1 * .25)/5 as decimal(5,0)) * 5
> select cast((@.num2 * .25)/5 as decimal(5,0)) * 5
>

rounding

How would I round the result of col1to the nearest 10th? The value of col1
(which is an average) is 0.423 in this case but I'm getting the resulting
value of ".0" rather than .4. If I use 0.00 as the second argument, I get
".00".
SELECT
avg(COALESCE(col1*1.0,0.0)) as col1,
FROM table1
_____
DC GSomething like this?
select cast(round(0.423, 1) as decimal(12,1))
ML

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
170 --> 170
96.58 --> 97
thanks
Here are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?

so
from -- to
----
170 --170
96.58 --97

thanksHere are a couple of ways

SELECT ROUND(170,0)
SELECT ROUND(96.58,0)

SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
--
170 --> 170
96.58 --> 97
thanksHere are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
--
170 --> 170
96.58 --> 97
thanksHere are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

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
(
@.DateSMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE@.Miniute TINYINT
DECLARE@.MultSMALLINT
DECLARE@.ValueTINYINT
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
On 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.googlegro ups.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.googlegro ups.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.

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.

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:
>
>
> > 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)- 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:
>> 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)
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...
>
> > On 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)
> 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.

round a value to the nearest integer

how do i round a value to the nearest integer?

Quote:

Originally Posted by poopsy

how do i round a value to the nearest integer?

Make use of ROUND()
Check here for detail