将秒转换为“00:00:00”格式 - SQL服务器 [英] Convert seconds to "00:00:00" format - SQL server

问题描述

我有一份报告，其中我必须计算一周内任何员工的整体工作时间。该报告反映的时间超过24小时（或86400秒）。我想将所有秒转换为00:00:00格式，保持列的格式为日期时间或时间，以便我可以使用时间字段进行进一步分析。



这段代码给了我我需要的东西，但是上面把INT中的秒转换为varchar，这不是我需要的报告。我需要输出为日期时间/时间格式，对于给定的示例，我可以给出结果，如'40：00：00'。

我希望我能够提供所有信息。请帮忙，因为我很多天都在努力解决这个问题。



我尝试了什么：



以下是我到目前为止所尝试的内容：

I have a report in which i have to calculate overall worked hours by any employee in a week. The report reflects hours more than 24 hours(or 86400 seconds). I want to convert all the seconds into "00:00:00" format keeping the format of the columns as datetime or time so that I can do further analysis using the time field.

This code gives me what I need however the above converts the seconds in INT to varchar which is not what I need for the report. I need the output to be in datetime/time format which can give me results like '40:00:00' for the given example.
I hope I am able I have provided all the information. Please help as I am struggling with this from many days.

What I have tried:

Below is what I have tried so far:

Declare @WorkTimeInSeconds int
Set @WorkTimeInSeconds = 144000 --This value is an example. Mostly values are above 86400 seconds
Select ActualHoursWorked =       convert(varchar,(@WorkTimeInSeconds)/3600)
            + ':' + right( '0' + convert(varchar,((@WorkTimeInSeconds) %3600)/60),2)
            + ':' + right( '0' + convert(varchar,(@WorkTimeInSeconds) %60),2)

推荐答案

问题是00:00:00不是日期时间格式 - 它是一个时间跨度。日期时间值需要一个日期，并且作为时间组件限制为23:59:99，因此您不能只说90,909秒是25:15:09的日期时间，因为这不是有效的日期时间值。

我要么将其作为NVARCHAR返回，要么将其返回几秒钟，然后让演示软件完全按照它想要显示的内容进行排序。 （或者可能是三个独立的INT值，但同样是凌乱的。）

The problem is that 00:00:00 is not a date time format - it's a timespan. A date time value requires a date, and is limited to 23:59:99 as the time component as well, so you can't just say "90,909 seconds is a datetime of 25:15:09" because that isn't a valid datetime value.
I'd either return it as a NVARCHAR or a number of seconds and let the presentation software sort out exactly what it wants to display. (Or possibly as three separate INT values, but that's equally messy).

这篇关于将秒转换为“00:00:00”格式 - SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！