rounding datetime nnn to whole seconds

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

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

|||

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

|||

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

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

>

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