0

I need to get a total of hours worked but my result always ends up in a rounded number. I need to show the real hours worked to two decimal places. Here is my code thus far:

CAST((datediff(mi,CONVERT(datetime,p.Punchin,114),CONVERT(datetime,p.Punchout,114)) - datediff(mi,CONVERT(datetime,p.Lunch_in,114),CONVERT(datetime,p.Lunch_out,114)))/60 AS decimal (12,2)) AS "Hours Worked"

Not sure where I am going wrong but assistance is greatly appreciated.

Kevin_H
  • 23
  • 4
  • It always helps if you tag your question with the specific database system you are using (e.g. `sql-server`) as there are variations in SQL syntax across different technologies and platforms. – Nathan Griffiths Nov 29 '19 at 01:24

2 Answers2

1

This is happening because of data type precedence. Since you're working with both integers on both sides of the expression, it's being implicitly converted to INT. Try diving by "60.00" or "CAST(60 AS DECIMAL(12,2))".

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver15

sam yi
  • 4,806
  • 1
  • 29
  • 40
0

Looks like you're getting the total number of minutes (an integer) and then dividing by 60 (an integer). Dividing an integer by an integer is integer division and will result in an integer with the fractional part dropped. THEN you're casting that integer to a decimal(12,2) but the fractional part is already gone.

Try changing /60 to /60.0 to avoid the integer division. I think that should fix it for you.

You can find more discussion of integer division in SQL server in these posts:
Integer division in sql server
How to get a float result by dividing two integer values using T-SQL?

sql_knievel
  • 1,199
  • 1
  • 13
  • 26