my code gives TOTAL HOURS in hours, but i am trying to output something like
TotalHours
8:36
where 8 represents hour part and 36 represents minutes part mean totalHours a person has worked in a single day at office.
with times as (
SELECT t1.EmplID
, t3.EmplName
, min(t1.RecTime) AS InTime
, max(t2.RecTime) AS [TimeOut]
, t1.RecDate AS [DateVisited]
FROM AtdRecord t1
INNER JOIN
AtdRecord t2
ON t1.EmplID = t2.EmplID
AND t1.RecDate = t2.RecDate
AND t1.RecTime < t2.RecTime
inner join
HrEmployee t3
ON t3.EmplID = t1.EmplID
group by
t1.EmplID
, t3.EmplName
, t1.RecDate
)
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
from times
Order By EmplID, DateVisited
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.
The DATEDIFF() function returns a value of integer indicating the difference between the start_date and end_date , with the unit specified by date_part . The DATEDIFF() function returns an error if the result is out of range for integer (-2,147,483,648 to +2,147,483,647).
MySQL TIMEDIFF() Function The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
Very simply:
CONVERT(TIME,Date2 - Date1)
For example:
Declare @Date2 DATETIME = '2016-01-01 10:01:10.022'
Declare @Date1 DATETIME = '2016-01-01 10:00:00.000'
Select CONVERT(TIME,@Date2 - @Date1) as ElapsedTime
Yelds:
ElapsedTime
----------------
00:01:10.0233333
(1 row(s) affected)
Try this query
select
*,
Days = datediff(dd,0,DateDif),
Hours = datepart(hour,DateDif),
Minutes = datepart(minute,DateDif),
Seconds = datepart(second,DateDif),
MS = datepart(ms,DateDif)
from
(select
DateDif = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')) aa
) a
Output
DateDif StartDate EndDate Days Hours Minutes Seconds MS
----------------------- ----------------------- ----------------------- ---- ----- ------- ------- ---
1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913
(1 row(s) affected)
Small change like this can be done
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, CASE WHEN minpart=0
THEN CAST(hourpart as nvarchar(200))+':00'
ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
FROM
(
SELECT EmplID, EmplName, InTime, [TimeOut], [DateVisited],
DATEDIFF(Hour,InTime, [TimeOut]) as hourpart,
DATEDIFF(minute,InTime, [TimeOut])%60 as minpart
from times) source
I would make your final select as:
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60) + ':' +
RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2)
as TotalHours
from times
Order By EmplID, DateVisited
Any solution trying to use DATEDIFF(hour,...
is bound to be complicated (if it's correct) because DATEDIFF
counts transitions - DATEDIFF(hour,...09:59',...10:01')
will return 1 because of the transition of the hour from 9 to 10. So I'm just using DATEDIFF
on minutes.
The above can still be subtly wrong if seconds are involved (it can slightly overcount because its counting minute transitions) so if you need second or millisecond accuracy you need to adjust the DATEDIFF
to use those units and then apply suitable division constants (as per the hours one above) to just return hours and minutes.
Just change the
DATEDIFF(Hour,InTime, [TimeOut]) TotalHours
part to
CONCAT((DATEDIFF(Minute,InTime,[TimeOut])/60),':',
(DATEDIFF(Minute,InTime,[TimeOut])%60)) TotalHours
The /60 gives you hours, the %60 gives you the remaining minutes, and CONCAT lets you put a colon between them.
I know it's an old question, but I came across it and thought it might help if someone else comes across it.
Divide the Datediff
in MS by the number of ms in a day, cast to Datetime
, and then to time:
Declare @D1 datetime = '2015-10-21 14:06:22.780', @D2 datetime = '2015-10-21 14:16:16.893'
Select Convert(time,Convert(Datetime, Datediff(ms,@d1, @d2) / 86400000.0))
If you want 08:30 ( HH:MM) format then try this,
SELECT EmplID
, EmplName
, InTime
, [TimeOut]
, [DateVisited]
, RIGHT('0' + CONVERT(varchar(3),DATEDIFF(minute,InTime, TimeOut)/60),2) + ':' +
RIGHT('0' + CONVERT(varchar(2),DATEDIFF(minute,InTime,TimeOut)%60),2)
as TotalHours from times Order By EmplID, DateVisited
Please put your related value and try this :
declare @x int, @y varchar(200),
@dt1 smalldatetime = '2014-01-21 10:00:00',
@dt2 smalldatetime = getdate()
set @x = datediff (HOUR, @dt1, @dt2)
set @y = @x * 60 - DATEDIFF(minute,@dt1, @dt2)
set @y = cast(@x as varchar(200)) + ':' + @y
Select @y
Difference Two Time in [hh:mm:ss]
select FORMAT((CONVERT(datetime,'2021-12-01 19:24:40') - CONVERT(datetime,'2021-12-01 17:00:00')),'hh:mm:ss')DffTime
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With