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),  "
NerishaBAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor 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
 
kamindaCommented:
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
 
NerishaBAuthor 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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Pratima PharandeCommented:
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
 
kamindaCommented:
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
 
NerishaBAuthor 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
 
NerishaBAuthor Commented:
@ kaminda,

I tried that, get a resuklt of "-6", not what I am looking for.
0
 
Pratima PharandeCommented:
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
 
s_niladriCommented:
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
 
s_niladriCommented:
In my solution total time work will always return in form of second.
0
 
NerishaBAuthor Commented:
@ pratima:

Both results give me an error of:

"Incorrect syntax near 'CAST', expected 'AS'."
0
 
kamindaCommented:
Can you post your whole query, looks like something is wrong in your logic
0
 
Pratima PharandeCommented:
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
 
Pratima PharandeCommented:
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
 
NerishaBAuthor 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
 
NerishaBAuthor 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
 
Pratima PharandeCommented:
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
 
NerishaBAuthor Commented:
That doesn't make a difference.  Get the exact same result.
0
 
Pratima PharandeCommented:
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
 
Anthony PerkinsCommented:
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
 
NerishaBAuthor 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
 
Pratima PharandeCommented:

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
 
NerishaBAuthor Commented:
Funny, I still get the same error...
0
 
NerishaBAuthor 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.

All Courses

From novice to tech pro — start learning today.