rounding datetime nnn to whole seconds

# rounding datetime nnn to whole seconds

• Friday, November 18, 2005 4:52 PM

First of all my apologies if this has been covered elsewhere, I have been unable to find it despite a good few hours searching, okay here we go:

I have an mssql database with a table storing a datetime as >
2005-10-06 16:04:04.933

I have data in an access table that stores the same time as>
2005-10-06 16:04:05

i.e. it has rounded up the time. (under .500 gets rounded down)
I need to perform the same rounding on the mssql table (using an update)
I can't use cast as smalldatetime is too small, timestamp isn't right either.

I've tried convert: but this doesn't achieve rounding correctly (it only rounds down)

I would assume there is a function to accomplish my task . . . if only I could find it.

If any-one could help I would be most greatfull.

With thanks
Michael

### All Replies

• Friday, November 18, 2005 7:00 PM

I do not know of any built in way of doing this, but you could create your own function to accomplish this.

declare @d as datetime
Set @d = GetDate()
--Set @d = '2005-10-06 16:04:04.933'
--Set @d = '2005-10-06 16:04:04.433'

declare @charDate as varchar(10)
Set @charDate = Convert( varchar, @d, 101)

declare @Hour as int
declare @Minute as int
declare @Second as int
declare @MiliSecond as int

Set @Hour = DatePart(hh, @d)
Set @minute = DatePart(mi, @d)
Set @Second = DatePart(ss, @d)
Set @MiliSecond = DatePart(ms, @d)

declare @roundedDate as datetime
Set @roundedDate = Convert( datetime, @charDate, 101)
Set @roundedDate = DateAdd( hh, @Hour, @roundedDate)
Set @roundedDate = DateAdd( mi, @Minute, @roundedDate)
Set @roundedDate = DateAdd( ss, @Second, @roundedDate)
if( @MiliSecond > 500 )
Set @roundedDate = DateAdd( ss, 1, @roundedDate)

Select @roundedDate

• Friday, November 18, 2005 8:41 PM

You can achieve this by using DATEPART and DATEADD functions.

declare @v datetime
set @v = '2005-11-18 12:33:02.231'

select case when datepart(ms, @v) >= 500 then dateadd(ms, 1000-datepart(ms, @v), @v) else dateadd(ms, -datepart(ms, @v), @v) end

• Saturday, November 19, 2005 6:55 PM

```Michael,

I think this inline expression may be a bit more efficient:

Steve Kass
Drew University

kudosdude@discussions.microsoft.com wrote:
> First of all my apologies if this has been covered elsewhere, I have
> been unable to find it despite a good few hours searching, okay here we
> go:
>
> I have an mssql database with a table storing a datetime as >
>  2005-10-06 16:04:04.933
>
> I have data in an access table that stores the same time as>
>  2005-10-06 16:04:05
>
> i.e. it has rounded up the time. (under .500 gets rounded down)
> I need to perform the same rounding on the mssql table (using an update)
> I can't use cast as smalldatetime is too small, timestamp isn't right
> either.
>
> I've tried convert: but this doesn't achieve rounding correctly (it only
> rounds down)
>
> I would assume there is a function to accomplish my task . . . if only I
> could find it.
>
> If any-one could help I would be most greatfull.
>
> With thanks
> Michael
>
```

• Monday, November 21, 2005 10:12 AM

`Firstly thanks for all the responses, I didn't think there was a function to do it.I would probably have done it similar to your way billrob & junn, but I have to say Steve Kass is a particurly elegant solutionMichael`