# SQL - Convert datetime calculation to Decimal

Hi,

I have a SQL query with the following line in it to calculate the total number of hours worked by an employee, based on the times the employee clocked in and out of the building. The calculation produces a result of type "varchar".  How can I get it to produce a result of type "decimal"?

the query is below"

"TotalHours_Worked =
convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),  "
###### Who is Participating?

Commented:
Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 60) + CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int)/3600 as varchar(4))

OR

Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST(((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 60) + CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int))/3600 as varchar(4))
0

Commented:
This will work

SELECT convert(decimal,sum(datediff(second, '2011-04-01', '2011-04-02')))

Why you are adding 0 seconds to the sum value?
0

Author Commented:
That is giving me a result of "-21784".  It should give me a result of something like 10.5, if the person worked for 10 and a half hours.
0

Commented:
are you sayinmg you are geeting correct resukt but its in varchar that you want to convert to decimal is it ?

then try this

SELECT CAST(convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) AS decimal(4,2))
0

Commented:
Ok now I see what you want exactly;

You can use something like this

SELECT convert(decimal,sum(datediff(minute, ClockIn,ClockOut)))/60

If I apply it to your query

TotalHours_Worked =
(convert(decimal,sum(datediff(minute, ClockIn,ClockOut)))/60),
0

Author Commented:
Yes, I am getting the correct result at the moment, but i need the result in decimal format.

I am gettng "10:30:12".  I want the result to be "10.5".

I tried the statement you gave me, it gives me an error:
"Error converting data type varchar to numeric.
Warning: Null value is eliminated by an aggregate or other SET operation."
0

Author Commented:
@ kaminda,

I tried that, get a resuklt of "-6", not what I am looking for.
0

Commented:
Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST(CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 as varchar(4))

OR

Select CAST (substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST(CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 as varchar(4)) AS decimal(4,2)
0

Commented:
Hi,
In the above solution time part is missing in date. the above query always return 0 if both datse are same.
Try the following one
Select convert(decimal, sum(datediff(second, '2010-05-27 15:23:37', '2010-05-27  15:28:35')), 0)

0

Commented:
In my solution total time work will always return in form of second.
0

Author Commented:
@ pratima:

Both results give me an error of:

"Incorrect syntax near 'CAST', expected 'AS'."
0

Commented:
Can you post your whole query, looks like something is wrong in your logic
0

Commented:
Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 as varchar(4))
0

Commented:
Select CAst (substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 as varchar(4)) as decimal(4,2))
0

Author Commented:
; with cte as (select  Emp.Name + ' ' + Emp.Surname As FullName, C.ClockTimes, Dir.Name,  SDay.Normal, row_no = row_number()
over (partition by Emp.Name + ' ' + Emp.Surname order by C.ClockTime)
from Employee Emp INNER JOIN Clocks C ON Emp.Emp_Id = C.Emp_ID
INNER JOIN Direction Dir ON RC.Direction_ID = Dir.Direction_ID
INNER JOIN EmpShift ON Emp.Emp_ID = EmpShift.Employee_ID
INNER JOIN Rules ON Rules.Rules_ID = EmpShift.Rules_ID
INNER JOIN SDay ON Rules.Rules_ID = SDay.Rules_ID),
Cte2 as (  select c1.Emp_ID, c1.Rules_ID, c1.SDay_ID, c1.FullName,
ClockIn = c1.ClockTime, ClockOut = case when c2.Name = 'OUT'
then c2.ClockTime else dateadd(day, datediff(day, 0, c1.ClockTime), 0) +
DateAdd(dd, DateDiff(dd, 0, c1.DefaultShiftEnd), 0) end,   c1.NormalDay
from cte c1 left join cte c2 on c1.row_no = c2.row_no - 1  and c1.FullName = c2.FullName
where c1.Name = 'IN')
select FullName, Date = convert(nvarchar(10),ClockIn, 111),  MIN(ClockIn) As Calc_first_clock,
MAX(ClockOut) As Calc_last_clock,  Calc_work_start_time = case when StartRounding_Up = 1
then  convert(smalldatetime,ROUND(cast(MIN(ClockIn) as float) *
(24/StartRounding_Hrs_Num),0)/(24/StartRounding_Hrs_Num))  when StartRounding_Up = 0
then DATEADD(Hour, DATEDIFF(Hour, 0, MIN(ClockIn)), 0) end,  Calc_work_end_time =
case when EndRounding_Up = 1 then  convert(smalldatetime,ROUND(cast(MIN(ClockOut) as float) *
(24/EndRounding_Num_Hrs),0)/(24/EndRounding_Num_Hrs))  when EndRounding_Up = 0
then DATEADD(Hour, DATEDIFF(Hour, 0, MIN(ClockOut)), 0) end,
TotalHours_Worked =
convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108),
FROM cte2
GROUP BY  convert(nvarchar(10), ClockIn, 111),FullName
0

Author Commented:
Thanks pratima, just one issue with your answer, if the total hours is something like "04:57:58", I get a result of "4.95".  When I checked this, I actually should be getting "4.96".
Is there some sort of rounding happening?
0

Commented:
try this

Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 + (CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int)/60  as varchar(4))
0

Author Commented:
That doesn't make a difference.  Get the exact same result.
0

Commented:
Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 + Round((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int)/60) as varchar(4))
0

Commented:
I guess I am being dense, I would have thought it would be as easy as:
DATEDIFF(second, ClockIn, ClockOut) / 3600.0

So, I am sure I am missing something...
0

Author Commented:
@ pratima:

I am getting an error at line:

Round((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int)/60) As varchar(4))

Error: "Incorrect syntax near the keyword 'As'."
0

Commented:

Select substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) , 1,2) + "." + CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,4,2) as Int) * 10)/6 + Round(CAST((CAST(substring( convert(varchar(10), dateadd(second, sum(datediff(second, ClockIn, ClockOut)), 0), 108) ,7,2) as Int)/60 as deciman(4,2))) as varchar(4))
0

Author Commented:
Funny, I still get the same error...
0

Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.