Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

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),  "
0
NerishaB
Asked:
NerishaB
  • 10
  • 8
  • 3
  • +2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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) * 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
 
NerishaBAuthor Commented:
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now