rounding datetime nnn to whole seconds

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

SQL Server 2000 Forums

Transact-SQL (2000)

rounding datetime nnn to whole seconds

Author

Topic

clockwork123
Starting Member

3 Posts

Posted - 2005-11-18 : 12:20:58

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

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-18 : 13:23:49

[code]
USE Northwind
GO

CREATE FUNCTION udf_TulsaTime (@dt datetime)
RETURNS varchar(25)
AS
BEGIN
RETURN CONVERT(varchar(25),DATEADD(ss, CASE WHEN DATEPART(ms,@dt) > 500 THEN 1 ELSE 0 END, @dt),120)
END

DECLARE @dt datetime
SELECT @dt = '2005-10-06 16:04:04.933'
SELECT dbo.udf_TulsaTime(@dt)

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-18 : 14:37:06

Try this:


select 
	Dt,
	[Datetime Rounded] =
	dateadd(ms,
	case when datepart(MS,DT) > 499
	then 1000
	else 0
	end - datepart(MS,DT),DT)
from
(
-- test dates
select dt = convert(datetime,'2005-11-18 14:02:30.000')
union all
select dt = convert(datetime,'2005-11-18 14:02:30.497')
union all
select dt = convert(datetime,'2005-11-18 14:02:30.500')
union all
select dt = convert(datetime,'2005-11-18 14:02:30.997')
) a


Dt                      Datetime Rounded
----------------------- -----------------------
2005-11-18 14:02:30.000 2005-11-18 14:02:30.000
2005-11-18 14:02:30.497 2005-11-18 14:02:30.000
2005-11-18 14:02:30.500 2005-11-18 14:02:31.000
2005-11-18 14:02:30.997 2005-11-18 14:02:31.000

CODO ERGO SUM

clockwork123
Starting Member

3 Posts

Posted - 2005-11-21 : 04:46:16

So no built in functions then? I didn't think so.
I was thinking of something similar, but these are both excellent solutions, thankyou both.

Michael

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 07:11:33

Nice Logic MVJ

Madhivanan

Failing to plan is Planning to fail

clockwork123
Starting Member

3 Posts

Posted - 2005-11-22 : 05:19:04

Just thought you may wish to see an alternate solution:

---@v is the date in question
dateadd(ms,500-datepart(ms,@v + '00:00:00.500'),@v)

With thanks to Steve Kass
Drew University

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-22 : 05:53:10

Good to see more than one solutions to a problem

-----------------
[KH]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 10:01:28

That's a nice solution. You could also do that same logic this way:

dateadd(ms,500-((datepart(ms,@v)+500)%1000),@v)

quote:
Originally posted by clockwork123

Just thought you may wish to see an alternate solution:

---@v is the date in question
dateadd(ms,500-datepart(ms,@v + '00:00:00.500'),@v)

With thanks to Steve Kass
Drew University

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources