How to Round a date to the nearest second

Question

Post reply

How to Round a date to the nearest second

raysteve43

Old Hand

Points: 333
More actions
September 8, 2014 at 12:23 pm

#299156

I am trying to figure out how to round a datetime variable to the nearest second. The solution must NOT just strip off the milliseconds, it needs to round.
Also, the solution should not be too cumbersome because it will be used in a high volume environment.
Any help is much appreciated
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This might work for you.

DECLARE @Today datetime = DATEADD( dd, DATEDIFF( dd, 0, GETDATE()), 0)

SELECT GETDATE(),

DATEADD( ms,

ROUND( DATEDIFF( ms, DATEADD( dd, DATEDIFF( dd, 0, GETDATE()), 0), GETDATE()), -3),

DATEADD( dd, DATEDIFF( dd, 0, GETDATE()), 0)),

DATEADD( ms,

ROUND( DATEDIFF( ms, @Today, GETDATE()), -3),

@Today);

It should be fast despite of all the functions used.

The first column is to show the actual datetime, the next is the one you should use and the third is made to show what is it done in the second one.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

And I forgot to post the easy way to do it. :w00t:

SELECT CAST(GETDATE() AS datetime2(0));

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Just for grins here is another way to do this. This is a little convoluted but I want to use integer math for this. 😀

create table #SomeDates (MyDate datetime)

insert #SomeDates

select GETDATE()

select MyDate

, dateadd(millisecond, case when (DATEPART(millisecond, MyDate) / 500) = 1 then (1000 - DATEPART(millisecond, MyDate)) else -1 * DATEPART(millisecond, MyDate) end, MyDate)

from #SomeDates

drop table #SomeDates

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101130 More actions · Answer 4

You'll also need to decide if you're willing to allow the rounding to change the day/date or not. That is, if the time is, say, "20140908 23:59:59.670", do you want to round to "20140909 00:00:00"?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 5

Sean Lange (9/8/2014)
Just for grins here is another way to do this. This is a little convoluted but I want to use integer math for this. 😀
create table #SomeDates (MyDate datetime)
insert #SomeDates
select GETDATE()
select MyDate
, dateadd(millisecond, case when (DATEPART(millisecond, MyDate) / 500) = 1 then (1000 - DATEPART(millisecond, MyDate)) else -1 * DATEPART(millisecond, MyDate) end, MyDate)
from #SomeDates
drop table #SomeDates

Certainly made me grin:-P and come up with a "case less" version of the same

😎

DECLARE @UNROUNDED DATETIME = GETDATE();

SELECT

@UNROUNDED AS UNROUNDED_DATE

,DATEADD(

SECOND

,(DATEPART(MILLISECOND, @UNROUNDED) / 500)

,DATEADD(MILLISECOND,-(DATEPART(MILLISECOND, @UNROUNDED)),@UNROUNDED)

) AS ROUNDED_DATE

In my opinion, given the fact that this has to be slim as it's supposed to be used in a high volume environment, the exercise of rounding is somewhat a pointless extra work. All it does is setting the boundaries to HH:MM:SS.500 so why not use that directly?

Edit: typo