T-SQL Statement for calculating time

[Tiger955]

Hi!
I run a Pass through query against a SQL-Server from Access 2010.

I have a field TimeWorked which has values like 1899-12-30 03:30:00.000, for 3:30 hours and a field PaymentPerHour with values like 10.50 (for EUR 10,50).

The result schould be 36,75 (36.75).

I tried to calculate like this:
Round(convert(varchar,[TimeWorked],104)*1440*[PaymentPerHour ]/60,2) AS TtlPayment

In an Access query it works with the connected table in the accb, but not against the server.

I get a lot different errors when trying to convert into int, datetime, etc. but could not solve my problem.

Pls help me to calculate the result.

Thanks
Michael

 

[MarkK]

Try to cast or convert your time to float, not varchar. I think varchar is a character as opposed to a numeric type.

 

[pbaldy]

I do this type of thing to convert a field containing a time to a decimal value I can multiply with:

Round(Cast(DatePart(Hour, [TimeField]) as Money) + (Cast(DatePart(Minute, [TimeField]) as Money)/60),2) AS HoursDecimal

 

[Tiger955]

Thanks, I found a solution in

Round(((datepart(hour,[TimeWorked])*60)+datepart(minute,[TimeWorked]))*[PaymentPerHour]/60,2) AS TtlPayment