Solved

# T-Sql Converting One time value to another

Posted on 2014-01-27
305 Views
Hi All,

I have a requirement to tie records into billings based on a contract.  So for instance we may have a per minute rate based in the contract, but the value might be stored in seconds or hours.

I have a flag table (called timeIncrements) that has an ID and a name,

so

ID             Name
0      N/A
1      Second
2      Minute
3      Hour
4      Day
5      Week
6      Month
7      Year

I was thinking of adding a "To Next Level" definition (eg: Second would have 60 and hours would have 24) and then having a while loop that can go between them, but was wondering if someone might have something more elegant out there.

I'm thinking this should probably end up being a scalar function
select convertTime(@value, @fromIncrement, @toIncrement)

with an output of something like:
select convertTime(3600, 1, 3)  -- 1
select convertTime(3600, 1, 4)  -- 1/24 = .0416
select convertTime(1, 3, 2) -- 60
select convertTime(1, 4, 2) -- 1440

Hope that makes sense.  Using  Sql 2008 R2.
0
Question by:Kyle Abrahams

LVL 68

Accepted Solution

Qlemo earned 500 total points
ID: 39813041
One way is to store an absolute factor in timeIncrements, e.g. based on seconds, and then just select those two factors and divide them.

E.g. timeIncrements:
``````ID   Name      Factor
0    N/A            1
1    Second         1
2    Minute        60
3    Hour        3600
4    Day        86400
5    Week      604800
6    Month   18144000
7    Year   217728000
``````
The result then is simply
``````@value * (select factor from timeIncrements where ID = @fromIncrement)
/ (select factor from timeIncrements where ID = @toIncrement)
``````
0

LVL 39

Author Comment

ID: 39813638
``````create function fn_ConvertValueToTimeIncrement( @value decimal(18,6), @fromIncrement int, @toIncrement int)
returns decimal (18,6)
as

begin

select @fromIncrement = (select factor from timeIncrements where Id = @fromIncrement)

select @toIncrement =  (select factor from  timeIncrements where id = @toIncrement)

declare @result decimal(18,6)

-- my own error checking for static values  N/A was set to 0
if @fromIncrement = 0 or @toIncrement = 0
set @result = 0
else if @fromIncrement = -1 or @toIncrement = -1
set @result = @value
else
set @result =
( select
@value
* @fromIncrement
/ @toIncrement
)

return @result
end

GO
``````

That works well with no additional time added for the query.  (test on 58,300 rows) . . . 2 seconds to return results either way.

Thanks.
0

## Featured Post

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties