Below is the table I have created and inserted values in it:

CREATE TABLE Attendance
(
  ClockedIn  DATETIME, 
  ClockedOut DATETIME
)

INSERT INTO Attendance(ClockedIn, ClockedOut) 
VALUES ('20160101 09:30:00','20160101 18:30:00'),
('20160201 08:10:00','20160201 18:20:00')

Now I am trying to solve a query which will display a person who has attended or worked for how many hours on that particular date. The query result that I want to should display something like which is shown below:

Clocked In                    Clocked Out                  Hours
2016-01-01 09:30:00.000       2016-01-01 18:30:00.000      9.00
2016-02-01 08:10:00.000       2016-02-01 18:20:00.000      10.10

This is what I have tried:

SELECT ClockedIn, ClockedOut,
CONVERT(VARCHAR,DATEPART(HOUR,ClockedOut - ClockedIn))   ':'   CONVERT(VARCHAR,DATEPART(MINUTE,ClockedOut - ClockedIn)) AS 'Hours'
FROM Attendance

And I got the following output:

I almost got the result that I wanted but the only issue is I am getting 9:0 instead of 9:00.

I even tried replacing MINUTE with MM inside the DATEPART and I got the following result:

As you can see from screenshot above, I got a wrong result.

What changes do I need to make in my existing query in order to display 9:00 instead of 9:0 ?

CodePudding user response：

I'm assuming that your times are in whole minutes. Here's are some ways that avoid invoking format():

concat(
    datediff(second, ClockedIn, ClockedOut) / 3600, ':',
    right('0'   cast(datediff(second, ClockedIn, ClockedOut) % 3600 / 60 as varchar(2)), 2)
)

concat(
    datediff(second, ClockedIn, ClockedOut) / 3600,
    left(concat(':', datediff(second, ClockedIn, ClockedOut) % 3600 / 60, '0'), 3))

The trick for zero-padding it to add in the extra 0 but then trim it down to the number you really want.

CodePudding user response：

As an alternative, which is longer, but maybe clearer to see what is going on, you can split out the calculations using CROSS APPLY to avoid repeating them. And you can if you wish convert to a datatime in order to make use of FORMAT which allows full control over formatting.

SELECT ClockedIn, ClockedOut
   -- Pad out missing zeros and display.
   , RIGHT('0'   CONVERT(varchar(2),C2.Hh),2)   ':'   RIGHT('0'   CONVERT(varchar(2),C2.Mm),2) [Hours 1]
   -- Optional, convert to a datetime datatype and then use FORMAT for full control over formatting
   , FORMAT(CONVERT(datetime2(0), RIGHT('0'   CONVERT(varchar(2),C2.Hh),2)   ':'   RIGHT('0'   CONVERT(varchar(2),C2.Mm),2)   ':00'), 'HH:mm') [Hours 2]
FROM Attendance A
CROSS APPLY (
    -- Calculate seconds, because its a lower unit then the lowest required
    VALUES (CONVERT(int, DATEDIFF(second, A.ClockedIn, A.ClockedOut) * 1.0 / 60))
) C1 (DiffInMinutes)
CROSS APPLY (
    -- Calculates hours and minutes
    VALUES (C1.DiffInMinutes / 60, C1.DiffInMinutes % 60)
) C2 (Hh, Mm);

Returns:

CodePudding user response：

I have finally solved it

This is what I have done:

SELECT ClockedIn, ClockedOut,
    FORMAT((CONVERT(DATETIME,ClockedOut) - CONVERT(DATETIME,ClockedIn)),'h:mm') AS 'Hours'
FROM Attendance

And I got my desired output: