SQL Server Performance

Round off dates

Discussion in 'General Developer Questions' started by danny123, Dec 8, 2008.

  1. danny123 New Member

    Hi there,
    How can i round off dates in TSQL (SQL Sever 2000). For example
    If the time is 12:13 then its considered as 12:10 and if time is 12:18 then time is considered as 12:20
    Please advice.
    Thanks !
    danny123, Dec 8, 2008
    #2

  2. ranjitjain New Member

    Try this:DECLARE @d1 DATETIME,@div SMALLINT
    set
    @d1=getdate()set
    @div=(datepart(n,@d1))%10select
    CASE WHEN @div <=5 THEN DATEADD(n,-@div,@d1)WHEN
    @div >=5 THEN DATEADD(n,10-@div,@d1) END, @d1
    ranjitjain, Dec 10, 2008
    #3

  3. FrankKalis Moderator

    [quote user="ranjitjain"]
    Try this:DECLARE @d1 DATETIME,@div SMALLINT
    set @d1=getdate()
    set @div=(datepart(n,@d1))%10
    select CASE WHEN @div <=5 THEN DATEADD(n,-@div,@d1)
    WHEN @div >=5 THEN DATEADD(n,10-@div,@d1) END, @d1
    [/quote]
    You're not rounding the seconds and milliseconds. If that is a problem, probably somethng like this can work:
    SELECT DATEADD(minute, (DATEDIFF(minute, 0, @d1) + 5) / 10 * 10, 0), @d1
    FrankKalis, Dec 10, 2008
    #4

Share This Page