CODEWITHSUNDEEP
c#sql-serversql-server-2008sql-server-2012
user7538827
user7538827

Reputation:

Calculate time spent working with pause on SqlServer - C#

Hi guys I have such a problem with c #, then I wrote a program that calculates an employee's hours, Total Hours is calculated (Ore Fine - Ore Inizio - Pausa) Below is the query that computes . My problem is that the pause must be insertia in the hour, minute format (For example, 1.30) how do I calculate the total time that a employee worked with the query below? I hope I have explained

OreInizio is DateTime

OreFine is DateTime

Pausa is Float

Upvotes: 0

Views: 154

Answers (2)

Ullas
Ullas

Reputation: 11556

First take the difference between those two datetime column values and the subtract it with pausa * 60 (to make this to minutes).

Query

select [IdRisorseUmane], [IdUtente], [IdCantiere],
cast(cast((
datediff(minute, [OreInizio], [OreFine]) - ([Pausa] * 60)) as int) / 60 as varchar) + ':'  
+ right('0' + cast(cast((
datediff(minute, [OreInizio], [OreFine]) - ([Pausa] * 60)) as int) % 60 as varchar(2)), 2) 
as [TotaleOre]
from [RisorseUmane]
where [IdCantiere] = @id;

Don't pass values within single quotes like you did where IdCantiere='" + IdCantiere + "'.
It is open for SQL injection attack. Always use parameters.

Upvotes: 1

Dmitry
Dmitry

Reputation: 518

Try this

select 
  [IdRisorseUmane], 
  [IdUtente], 
  [IdCantiere],
  CONVERT(varchar(5), DATEADD(minute, DATEDIFF(minute, [OreInizio], [OreFine]) - cast([Pausa] * 60 as int), 0), 114) as [TotaleOre]
from [RisorseUmane]
where [IdCantiere] = @id;

Upvotes: 2

Related Questions