每天总工作时间 [英] Total Hours working Daily

问题描述

工时表中的总工时计算时数和分钟数.在数据库sql服务器表中，两列start_time和End_time.如何计算总时数您能帮我吗?

例如
--------------
Start_Time End_Time Hours&Min
上午10.00 11.15上午1.15
11.30 AM 12.00PM 0.30
--------------
总小时1.45我想要这个.

提醒
RAJESH

Hi,

Total Hours calculation Hours and Minutes as per Time Sheet. In Database sql server table two column start_time and End_time. How to calculate Total Hours Can u help me out please.

For Example
--------------
Start_Time End_Time Hours&Min
10.00 Am 11.15AM 1.15
11.30 AM 12.00PM 0.30
--------------
TOTAL HOURS 1.45 I WANT LIKE THIS.

REGARDS
RAJESH

推荐答案

看看是否有帮助...

1.首先找出所有行的总时间

See if this helps...

1. First find out the total time across all rows

declare @Total int
select @Total=  SUM(DATEDIFF(MI, StartDate , EndDate)) from [TableName]

2.接下来使用总时间将其转换为小时和分钟

2. Next using the Total time convert it to hours and minutes

SELECT CAST((@Total / 60) AS VARCHAR(8)) + ':' +         CAST((@Total % 60) AS VARCHAR(2)) as hourtime

JimJos的回答是正确的，但我想补充一点，我不建议在SQL中进行格式化.取而代之的是，从数据库中获取数据(在这种情况下，以分钟为单位的日期差异)，然后考虑到客户端使用的区域性，在客户端对其进行正确格式化.

The answer by JimJos is correct, but I would like to add that I wouldn''t advice to do the formatting in SQL. Instead, fetch the data from the database (date difference in minutes in this case) and then format it properly at the client taking into account the culture the client is using.

这篇关于每天总工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！