Need to round the time to within a tenth of a second

Sue_W
Sue_W used Ask the Experts™
on
I am having trouble with rounding my time to within 10th of a second. The data is in string format and I need it to read 00:00:02.8 instead of 00:00:02.7962796. I've tried the 'round' function but I get errors about it being a varchar but I don't see how I can convert it to a float, etc and keep the data integrity.

Help will be appreciated.

Thx,
Sue
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris LuttrellSenior Database Architect

Commented:
in 2008 it is easy just use new TIME datatype like this to get these results:
StringValue      RoundedTime
00:00:02.7962796      00:00:02.8
00:00:02.7362796      00:00:02.7

DECLARE @timestring VARCHAR(50)
 
SET @timestring = '00:00:02.7962796'
 
SELECT @timestring, CONVERT(TIME(1),@timestring)
 
SET @timestring = '00:00:02.7362796'
 
SELECT @timestring, CONVERT(TIME(1),@timestring)

Open in new window

Commented:

select convert(varchar(4),
 round(convert(int,substring(convert(varchar(30),getdate(),113),22,3)),-2))

this will return the seconds

Do you need to deal with the secs when you have 8:99

to round to 9?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
some sql:
declare @tmp varchar(100)
set @tmp = '00:00:02.7962796'
 
select @tmp
, convert(datetime, left(@tmp,8) ,8)
, cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) 
, round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,2)
, dateadd(millisecond, 1000 * round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,2), convert(datetime, left(@tmp,8) ,8))
, convert(varchar(10), dateadd(millisecond, 1000 * round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,2), convert(datetime, left(@tmp,8) ,8)), 114)

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Senior Database Architect
Commented:
as angelIII has pointed out, to do this in an older version of SQL will be ugly.  I hope you can use 2008.
He did convert it back to a string though, just wrap my expression with another convert to get it back to a string like this:

DECLARE @timestring VARCHAR(50)
 
SET @timestring = '00:00:02.7962796'
 
SELECT @timestring, CONVERT(VARCHAR(20),CONVERT(TIME(1),@timestring))

Open in new window

Chris LuttrellSenior Database Architect

Commented:
actuall there is a bug in angelIII's rounding which you see if you try this value instead (he rounded to 2 places instead of 1):
set @tmp = '00:00:02.7932796'
which should also round to 2.8 but results in 2.7.

his example is modified below to round to just the 1 place and results in this now:

00:00:02.7932796      1900-01-01 00:00:02.000      0.7932796000      0.8000000000      1900-01-01 00:00:02.800      00:00:02:8
declare @tmp varchar(100)
set @tmp = '00:00:02.7932796'
 
select @tmp
, convert(datetime, left(@tmp,8) ,8)
, cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) 
, round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,1)
, dateadd(millisecond, 1000 * round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,1), convert(datetime, left(@tmp,8) ,8))
, convert(varchar(10), dateadd(millisecond, 1000 * round(cast( '0.' + substring(@tmp, 10,10) as decimal(20,10)) ,1), convert(datetime, left(@tmp,8) ,8)), 114)

Open in new window

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
@CGLuttrell
yes, it should be round(... ,0) and not round(...,1)
thanks for the correction

Commented:
If you're in 2005, it's a little harder. But - you could do something like this:

declare @tmp varchar(50);
set @tmp = '00:00:02.7962796';

select dateadd(millisecond, case when substring(@tmp,11,1) between '5' and '9' then 100 else 0 end, cast(left(@tmp,10) as datetime));

The idea being that we're only interested the 10 leftmost characters in this, but we need to add 100 milliseconds if the 11th character is 5-9.

Rob
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Easiest to use date functions...



declare @d datetime

set @d = getdate()

select @d as raw_date, dateadd(ms, round( (datepart(ms,@d) / 1000.0),1)*1000 - datepart(ms,@d), @d) as rounded_date
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Ooops, missed the whole string thingy...

declare @d datetime
--set @d = getdate()
set @d = substring('00:00:02.7962796',1,10)

select convert(varchar,dateadd(ms, round( (datepart(ms,@d) / 1000.0),1)*1000 - datepart(ms,@d), @d),114) as rounded_time
Chris LuttrellSenior Database Architect

Commented:
mark, wouldn't using the substring there just truncate to 2.7 in stead of round to 2.8?

@Sue_W, what is your status and thoughts on this? you have had input from several of the best minds on EE in this subject area, are we on the right track to solve your question?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
The substring should be 12 - what would I do without you ?

declare @d datetime
--set @d = getdate()
set @d = substring('00:00:02.7962796',1,12)

select convert(varchar,dateadd(ms, round( (datepart(ms,@d) / 1000.0),1)*1000 - datepart(ms,@d), @d),114) as rounded_time

Also, need to clarify if it is SQL 2008 - makes a huge difference.

Author

Commented:
Hi Guys,

The database was just upgraded to 2008 from 2005 so I was able to use the 2008 time datatype. The new datatype is really cool because it rounds for you. I really appreciate seeing the other solutions in case I need this again for an older version of sql.  

Thanks Guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial