sql server - SQL: Column formula -

i trying create formula cost column calculates cost based on difference between other columns , not working.

parking_cost field have cost based on difference between parkingstarttime , parkingendtime * 5 since 1 hours = $5.00.

formula:

(convert([int],[parkingendtime]-[parkingstarttime]*5,(0))) 

i appreciate suggestion?

regards.

use datediff determine difference between dates/times. formula this:

datediff(minute, parkingstarttime, parkingendtime) / 60 * 5 

of course use just

datediff(hour, parkingstarttime, parkingendtime) * 5 

which simpler, might give undesired results in cases. example, if start time 8:55 , end time 9:05, datediff(hour, ...) return 1 although actual difference less, 10 minutes. it's because datediff in essence truncates times units specified , calculates difference.

so similar situation can minutes, @ least minutes have better granularity. example, receive answer of 60 when calculating datediff(minute, '5:20:57', '6:20:04'), though it's in fact more 59 minutes, inaccuracy not big can datediff(hour, ...).

if want have better granularity, calculate difference in seconds , divide 3600 whole hours:

datediff(second, parkingstarttime, parkingendtime) / 3600 * 5 

---

update

in sql server, when integer divided integer, result integer too.

if need result more precise, can ensure (using last expression above):

datediff(second, parkingstarttime, parkingendtime) / 3600.0 * 5