Solved

# SQL - Convert datetime calculation to Decimal

Posted on 2011-05-03
750 Views
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),  "
0
Question by:NerishaB

LVL 9

Expert Comment

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 Comment

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

LVL 39

Expert Comment

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

LVL 9

Expert Comment

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 Comment

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 Comment

@ kaminda,

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

LVL 39

Expert Comment

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

LVL 4

Expert Comment

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

LVL 4

Expert Comment

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

Author Comment

@ pratima:

Both results give me an error of:

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

LVL 9

Expert Comment

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

LVL 39

Expert Comment

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

LVL 39

Expert Comment

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 Comment

; 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 Comment

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

LVL 39

Expert Comment

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 Comment

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

LVL 39

Expert Comment

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

LVL 75

Expert Comment

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 Comment

@ 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

LVL 39

Expert Comment

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 Comment

Funny, I still get the same error...
0

LVL 39

Accepted Solution

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

Author Closing Comment

thanks
0

## Featured Post

Performance is the key factor for any successful data integration project, knowing the type of transformation that youâ€™re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternatâ€¦
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that â€¦
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.