Accurate Seconds - 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

Accurate Seconds

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-23 : 11:47:31

When I do a datediff to get Seconds its rounds of to 4 or 3 or 1 etc.
How to get the fractional Seccons like 4.67 etc

DATEDIFF(ss,TransDB_TradeDate,TransDB_UpdateTime)

Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 11:48:42

SELECT CAST(TransDB_UpdateTime - TransDB_TradeDate AS FLOAT) * 86400.0

E 12°55'05.25"
N 56°04'39.16"

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-23 : 11:58:27

Thanks Peso. Can you please explain how did you get this. What is the number 86400.00

I understand this TransDB_UpdateTime - TransDB_TradeDate AS FLOAT
give the diff in hrs, min and sec.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-23 : 12:07:47

quote:
Originally posted by avmreddy17

Thanks Peso. Can you please explain how did you get this. What is the number 86400.00

86400.00 is the number of seconds for a day
( 24hrs * 60 min * 60 sec )

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-23 : 12:10:09

Thanks Much

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-23 : 12:34:51

[code]

select
D1,
D2,
DD_Sec = datediff(ss,D1,D2),
DD_MS = datediff(ms,D1,D2)*.001
from
(
-- Test Data
select
D1 = convert(datetime,'20080723 11:23:45.337') ,
D2 = convert(datetime,'20080723 11:37:55.993')
) a

Results:
D1 D2 DD_Sec DD_MS
----------------------- ----------------------- ------ -------
2008-07-23 11:23:45.337 2008-07-23 11:37:55.993 850 850.656

(1 row(s) affected)

[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 13:06:22

MVJ, what happens if the two dates are more than 26 days apart?

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-23 : 14:19:01

quote:
Originally posted by Peso

MVJ, what happens if the two dates are more than 26 days apart?

E 12°55'05.25"
N 56°04'39.16"

You use a different algorithm.

I was just trying to avoid the casting of datetime to float, since casting datetime to float it is not really a supported method of datetime manipulation (to my knowledge).


select
	D1,
	D2,
	Peso	= 	cast(D2-D1 as float)*86400.0 ,
	DD_MS	= 	convert(bigint,datediff(hh,0,D2-D1))*3600.0+
			(datediff(ms,0,D2-D1-dateadd(hh,datediff(hh,0,D2-D1),0))*.001)
from
	(
	-- Test Data
	select
		D1 = convert(datetime,'17530101 00:00:00.997') ,
		D2 = convert(datetime,'98521230 23:59:59.443')
	) a


ResultsL
D1                      D2                      Peso               DD_MS
----------------------- ----------------------- ------------------ ----------------
1753-01-01 00:00:00.997 9852-12-30 23:59:59.443 255611203198.44669 255611203198.446

(1 row(s) affected)

CODO ERGO SUM

avmreddy17
Posting Yak Master

180 Posts

Posted - 2008-07-23 : 15:02:28

Is there a way to avoid 3.3333333333333333E-2 ( exponential values like this )
while using SELECT CAST(TransDB_UpdateTime - TransDB_TradeDate AS FLOAT) * 86400.0.

Can this values 3.3333333333333333E-2 be represented as 0.0333

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 18:05:15

You can cast as DECIMAL with a fixed number of decimals.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources