rounding datetime nnn to whole seconds

rounding datetime nnn to whole seconds

• maroy

  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

   

   

  
  

• imke

  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
  > 
  

  
  

• ChrisLanotte

  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
  

  
  

• Eisa

  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

  
  
• rounding datetime nnn to whole seconds