rounding datetime nnn to whole seconds

Question

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

Answer 1

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

 

 

Answer 2

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

Answer 3

Michael,

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

dateadd(ms,500-datepart(ms,@v + '00:00:00.500'),@v)

Steve Kass
Drew University

[e-mail] 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
> 

Answer 4

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 solution
Michael