rounding milliseconds problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

rounding milliseconds problem

Author

Topic

jordanblue
Starting Member

2 Posts

Posted - 2008-10-21 : 08:00:53

hi everyone...

does anyone know how i can round milliseconds to seconds?

from : 2008-10-21 19:59:14.923
to: 2008-10-21 19:59:15.000

thnx in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-10-21 : 08:48:52

This will work
declare @date datetime
set @date = '2008-10-21 19:59:14.923'

select

DATEADD(ms,ROUND(DATEDIFF(ms,DATEADD(day,DATEDIFF(day,0,@date),0),@date ),-3),DATEADD(day,DATEDIFF(day,0,@date),0))

Couldn't you just truncate the ms?
SELECT convert(smalldatetime,2008-10-21 19:59:14.923)
gives 2008-10-21 19:59:14.000

Jim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 08:58:20

quote:
Originally posted by jimf

Couldn't you just truncate the ms?
SELECT convert(smalldatetime,2008-10-21 19:59:14.923)
gives 2008-10-21 19:59:14.000

Jim

It would truncate seconds part too

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-21 : 11:11:48

[code]DECLARE @Sample DATETIME

SET @Sample = '2008-10-21 19:59:14.500'

SELECT @Sample,
CAST(CONVERT(CHAR(19), DATEADD(MILLISECOND, 500, @Sample), 120) AS DATETIME)[/code]

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-21 : 14:49:58

Here is a variation without casting to character and back to datetime, and it only uses two date function calls.

Edit: Added code to round down and up, as well as normal rounding.


select
	DT,
	-- Round down to nearest second
	DT_Floor_MS =
		dateadd(ms,-datepart(ms,a.DT),a.DT),
	-- Round up to nearest second
	DT_Ceiling_MS = 
		dateadd(ms,(1000-datepart(ms,a.DT))%1000,a.DT),
	-- Round to nearest second
	DT_Round_Off_MS =
		dateadd(ms,500-((datepart(ms,a.DT)+500)%1000),a.DT)
from
	( -- Test Data
	select DT = convert(datetime,'14:08:43.000')	union all
	select DT = convert(datetime,'14:08:43.003')	union all
	select DT = convert(datetime,'14:08:43.497')	union all
	select DT = convert(datetime,'14:08:43.500')	union all
	select DT = convert(datetime,'14:08:43.997')	union all
	select DT = convert(datetime,'14:08:44.000')	union all
	select DT = convert(datetime,'23:59:59.997')
	) a



Results:

DT                      DT_Floor_MS             DT_Ceiling_MS           DT_Round_Off_MS
----------------------- ----------------------- -------- -------------- -----------------------
1900-01-01 14:08:43.000 1900-01-01 14:08:43.000 1900-01-01 14:08:43.000 1900-01-01 14:08:43.000
1900-01-01 14:08:43.003 1900-01-01 14:08:43.000 1900-01-01 14:08:44.000 1900-01-01 14:08:43.000
1900-01-01 14:08:43.497 1900-01-01 14:08:43.000 1900-01-01 14:08:44.000 1900-01-01 14:08:43.000
1900-01-01 14:08:43.500 1900-01-01 14:08:43.000 1900-01-01 14:08:44.000 1900-01-01 14:08:44.000
1900-01-01 14:08:43.997 1900-01-01 14:08:43.000 1900-01-01 14:08:44.000 1900-01-01 14:08:44.000
1900-01-01 14:08:44.000 1900-01-01 14:08:44.000 1900-01-01 14:08:44.000 1900-01-01 14:08:44.000
1900-01-01 23:59:59.997 1900-01-01 23:59:59.000 1900-01-02 00:00:00.000 1900-01-02 00:00:00.000

(7 row(s) affected)

CODO ERGO SUM

jordanblue
Starting Member

2 Posts

Posted - 2008-10-22 : 10:15:10

Wow! you guys are great!

i've been working for that for hours..

Thanks guys ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources