-2

Let's assume Attendance Table has the following columns: Clocked In and Clocked Out, and Data Type for both the columns is DATETIME

For example, Clocked In column has the following Rows: 2016-01-01 09:30:00.000 and 2016-02-01 08:10:00.000.

For example, Clocked Out column has the following Rows: 2016-01-01 18:30:00.000 and 2016-02-01 18:20:00.000

Now I would like to calculate the time difference between Clocked In and Clocked Out which will display the output in the format of H:MM

To be more clear, Clocked Out(2016-01-01 18:30:00.000) - Clocked In (2016-01-01 09:30:00.000) should display the output as 9.00.

Clocked Out(2016-02-01 18:20:00.000) - Clocked In(2016-02-01 08:10:00.000) should display the output as 10:10

9:00 and 10:00 indicates number of hours an employee has worked or attended during that particular date.

I tried SELECT Clocked Out - SELECT Clocked FROM Attendance but it didn't work.

How can it be solved ?

pro_data
  • 151
  • 6
  • 1
    You are trying to subtract a string from a string? How is that going to work? Please read the documentation for [SQL Server datetime functions](https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15) – Dale K Mar 25 '22 at 21:47
  • Dale K ... I have edited the Question and also submitted a review for requesting to reopen the question. Have a look at my edited question and see if it is acceptable this time. Thanks. – pro_data Mar 25 '22 at 23:30
  • You have totally changed your question. The duplicate answered your original question, you need to rollback the changes you made to this question so that it still makes sense. Then ask another question about your new issue. Questions are not intended to be debugging sessions, so if the answer solves your initial issue but raises a new issue, you need to open a new question. – Dale K Mar 25 '22 at 23:33
  • Thanks for your suggestion. I have made changes again in a way to make sure that the question still makes sense. I will ask a new question in a different way and with different description. – pro_data Mar 26 '22 at 00:40

2 Answers2

1

You need to take the difference in minutes, determine how many hours that is, and how many minutes are left over. Given this data:

CREATE TABLE dbo.PunchClock
(
  ClockedIn  datetime, 
  ClockedOut datetime
);

INSERT dbo.PunchClock(ClockedIn, ClockedOut) VALUES
('20160101 09:30:00','20160101 18:30:00'),
('20160201 08:10:00','20160201 18:20:00');

To get the hours and minutes separately, we can say:

SELECT ClockedIn, ClockedOut,
  [H] = DATEDIFF(MINUTE, ClockedIn, ClockedOut)/60, 
  [M] = DATEDIFF(MINUTE, ClockedIn, ClockedOut)%60
FROM dbo.PunchClock;

Output:

ClockedIn ClockedOut H M
2016-01-01 09:30:00.000 2016-01-01 18:30:00.000 9 0
2016-02-01 08:10:00.000 2016-02-01 18:20:00.000 10 10

To combine them:

;WITH src AS 
(
  SELECT ClockedIn, ClockedOut,
    [H] = DATEDIFF(MINUTE, ClockedIn, ClockedOut)/60, 
    [M] = DATEDIFF(MINUTE, ClockedIn, ClockedOut)%60
  FROM dbo.PunchClock
)
SELECT ClockedIn, ClockedOut,
  CONCAT([H], ':', RIGHT('0' + CONVERT(varchar(2), [M]),2))
FROM src;

Output:

ClockedIn ClockedOut Hours (H:MM)
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
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
0

A bit convoluted, but works:

SELECT * , SUBSTRING(CAST(CAST([Clocked Out] - [Clocked In] as time(0)) as CHAR(8)), 1, 5) 
FROM Attendance

Fiddle DEMO

Without leading zeroes (even more convoluted):

SELECT * , 
   REPLACE(LTRIM(SUBSTRING(REPLACE(CAST(CAST([Clocked Out] - [Clocked In] as time(0)) as CHAR(8)), '0', ' '), 1, 5)), ' ', '0') 
FROM Attendance

Fiddle Update

EXPLANATION:

  1. CAST the date/time difference as time(0) - which is hh:mm:ss
  2. CAST the result as a 8-character string - CHAR(8)
  3. REPLACE all zeroes with spaces
  4. Extract SUBSTRING of the first 5 characters (hh:mm)
  5. Use LTRIM to remove leading spaces (created in Step 3)
  6. REPLACE all remaining spaces with zeroes (restoring non-leading zeroes)

Steps 3 and 4 could be swapped.

PM 77-1
  • 12,933
  • 21
  • 68
  • 111