CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Tayyab
Tayyab

Reputation: 69

SQL Server: Converting a number to decimal

I have a column which represent the values as seconds. The Result of this column are as (465109,352669,351949.... etc). I need the results to be always in decimals and less then one. So the 465109 result should come as 0.465109, Similarly the 352669 should come as 0.352669.

Is there a way i can do that in SQL Sever 2008? 

DATEDIFF(SECOND,StartDate,GETDATE())

Upvotes: 2

Views: 72

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

select @x = DATEDIFF(SECOND,StartDate,GETDATE());
select @x/power(10,len(@x));

You can try this by dividing with the length of integer returned by datediff function raised to the power of 10.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Can you just use division?

select seconds / 1000000.0

Note the .0 so it does floating point division rather than integer division.

If you want these as a decimal, then cast afterwards:

select cast(seconds / 1000000.0 as decimal(10, 6))

Upvotes: 1

shA.t
shA.t

Reputation: 16968

A trick is this:

CAST('.' + REPLACE(CAST(@value AS varchar(30)), '.', '') AS Numeric(28,10))

And as a math solution:

CAST(@valueAS Numeric(28,10)) / (POWER(10, LEN(CAST(FLOOR(@value) AS varchar(20)))))

Upvotes: 0

Related Questions