How to calculate timing hours and mints from datetime column

Question

Dear All, Can anyone support me i have one colunm which datetime and another column like emp id and i need to calculate there emp total hours and mints date wise from below.

Prasad Raveendran · Answer

take a look at the below code and see whether this would works for you -- Drop table if it exists DROP TABLE IF EXISTS EmployeeCheckTimes; -- Create table CREATE TABLE EmployeeCheckTimes ( CHECKTIME DATETIME, BADGENUMBER INT ); -- Insert data INSERT INTO EmployeeCheckTimes (CHECKTIME, BADGENUMBER) VALUES ('2024-05-01 00:00:09.000', 3001025), ('2024-05-02 00:09:10.000', 3001025), ('2024-05-01 00:00:09.000', 900511), ('2024-05-01 00:12:08.000', 900511), ('2024-05-01 00:00:21.000', 700677), ('2024-05-01 00:00:32.000', 700618), ('2024-05-01 00:00:45.000', 700447), ('2024-05-01 00:00:49.000', 101355), ('2024-05-01 00:00:52.000', 101534), ('2024-05-01 00:10:02.000', 101534), ('2024-05-01 00:01:09.000', 100066); -- Create index for performance improvement CREATE INDEX idx_badgenumber_checktime ON EmployeeCheckTimes (BADGENUMBER, CHECKTIME); -- Select query with CTE WITH CheckTimes AS ( SELECT BADGENUMBER, MIN(CHECKTIME) AS MIN_CHECKTIME, MAX(CHECKTIME) AS MAX_CHECKTIME FROM EmployeeCheckTimes GROUP BY BADGENUMBER ) SELECT BADGENUMBER, MIN_CHECKTIME, MAX_CHECKTIME, DATEDIFF(HOUR, MIN_CHECKTIME, MAX_CHECKTIME) AS [Hours], DATEDIFF(MINUTE, MIN_CHECKTIME, MAX_CHECKTIME) AS [Minutes] FROM CheckTimes; Using a CTE can sometimes improve readability and performance, although the performance gains are more situation-dependent.

Naveen Kumar · Answer

Hello Feroz, To calculate hours and minutes, we need two datetime values. However, I see only CHECKTIME in your provided data. Assuming BADGENUMBER is not a primary key and that the same BADGENUMBER can have multiple CHECKTIME entries, please see the query below to suit your requirement. DROP TABLE IF EXISTS EmployeeCheckTimes CREATE TABLE EmployeeCheckTimes ( CHECKTIME DATETIME, BADGENUMBER INT ); INSERT INTO EmployeeCheckTimes (CHECKTIME, BADGENUMBER) VALUES ('2024-05-01 00:00:09.000', 3001025), ('2024-05-02 00:09:10.000', 3001025), ('2024-05-01 00:00:09.000', 900511), ('2024-05-01 00:12:08.000', 900511), ('2024-05-01 00:00:21.000', 700677), ('2024-05-01 00:00:32.000', 700618), ('2024-05-01 00:00:45.000', 700447), ('2024-05-01 00:00:49.000', 101355), ('2024-05-01 00:00:52.000', 101534), ('2024-05-01 00:10:02.000', 101534), ('2024-05-01 00:01:09.000', 100066) SELECT BADGENUMBER, MIN(CHECKTIME) AS MIN_CHECKTIME, MAX(CHECKTIME) AS MAX_CHECKTIME, DATEDIFF(HOUR, MIN(CHECKTIME), MAX(CHECKTIME)) AS [Hours], DATEDIFF(MINUTE, MIN(CHECKTIME), MAX(CHECKTIME)) AS [Minutes] FROM EmployeeCheckTimes GROUP BY BADGENUMBER