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
This question is related to
sql
sql-server
In case someone is still searching for a query to display the difference in hr min and sec format: (This will display the difference in this format: 2 hr 20 min 22 secs)
SELECT
CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ' hrs ' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20)) + ' mins' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)% 60 as nvarchar(20)) + ' secs'
OR can be in the format as in the question:
CAST(DATEDIFF(minute, StartDateTime, EndDateTime)/ 60 as nvarchar(20)) + ':' + CAST(DATEDIFF(second, StartDateTime, EndDateTime)/60 as nvarchar(20))
Since any DateTime can be cast to a float, and the decimal part of the number represent the time itself:
DECLARE @date DATETIME = GETDATE()
SELECT CAST(CAST(@date AS FLOAT) - FLOOR(CAST(@date AS FLOAT)) AS DATETIME
This will result a datetime like '1900-01-01 hour of the day' you can cast it as time, timestamp or even use convert to get the formatted time.
I guess this works in any version of SQL since cast a datetime to float is compatible since version 2005.
Hope it helps.
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)
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))
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
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.
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)
this would hep you
DECLARE @DATE1 datetime = '2014-01-22 9:07:58.923'
DECLARE @DATE2 datetime = '2014-01-22 10:20:58.923'
SELECT DATEDIFF(HOUR, @DATE1,@DATE2) ,
DATEDIFF(MINUTE, @DATE1,@DATE2) - (DATEDIFF(HOUR,@DATE1,@DATE2)*60)
SELECT CAST(DATEDIFF(HOUR, @DATE1,@DATE2) AS nvarchar(200)) +
':'+ CAST(DATEDIFF(MINUTE, @DATE1,@DATE2) -
(DATEDIFF(HOUR,@DATE1,@DATE2)*60) AS nvarchar(200))
As TotalHours
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
No need to jump through hoops. Subtracting Start from End essentially gives you the timespan (combining Vignesh Kumar's and Carl Nitzsche's answers) :
SELECT *,
--as a time object
TotalHours = CONVERT(time, EndDate - StartDate),
--as a formatted string
TotalHoursText = CONVERT(varchar(20), EndDate - StartDate, 114)
FROM (
--some test values (across days, but OP only cares about the time, not date)
SELECT
StartDate = CONVERT(datetime,'20090213 02:44:37.923'),
EndDate = CONVERT(datetime,'20090715 13:24:45.837')
) t
Ouput
StartDate EndDate TotalHours TotalHoursText
----------------------- ----------------------- ---------------- --------------------
2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 10:40:07.9130000 10:40:07:913
See the full cast and convert options here: https://msdn.microsoft.com/en-us/library/ms187928.aspx
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.
Source: Stackoverflow.com