SQL - Convert datetime calculation to Decimal

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?

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.

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))

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),

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."

@ kaminda,

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

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)

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)

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

@ pratima:

Both results give me an error of:

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

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

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))

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))

; 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

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?

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))

That doesn't make a difference.  Get the exact same result.

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))

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...

@ 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'."

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))

Funny, I still get the same error...

thanks