如何在SQL SERVER 2008中只计算时间 [英] how to calculate only time in SQL SERVER 2008

问题描述

大家好，



i有开始时间和结束时间，



如何计算这个在SQL SERVER 2008中。

我必须采用什么数据类型？



仅限小时和分钟。



任何人都可以帮助我

Hi guys,

i have Starting time and ending time,

how can in calculate this in SQL SERVER 2008.
what datatype i have to take?

Only in Hours & Minutes.

Can anyone plz help me

推荐答案

尝试

SELECT DATEDIFF(mi, CAST('6:30' AS DATETIME), CAST('14:00' AS DATETIME)) / 60 AS Hours,
       DATEDIFF(mi, CAST('6:30' AS DATETIME), CAST('14:00' AS DATETIME)) % 60 AS Minutes

据我所知，SQL Server中没有数据类型，符合您要求的仅小时和分钟。

虽然有简单的方法。将它存储在INT字段中，并确保只在几分钟内保存结果。

公式：

As far as I know, there is no datatype exists in SQL Server, which matches your requirement as you asked for only hours and minutes.
There is simple way though. Store it in a INT field and make sure that you keep result only in minutes.
Formula:

x hours y minutes = (x*60)+y

示例：

Example:

2 hours 10 minutes (02:10) = (2*60)+10 = 130

在退休时你可以得到结果

and while retiving you can get the result as

SELECT CAST((t/60) AS VARCHAR)+':'+CAST((130%60) AS VARCHAR) AS Result

要正确格式化，

To format correctlly,

SELECT RIGHT('0'+CAST((t/60) AS VARCHAR),2)+':'+CAST((t%60) AS VARCHAR) AS Result

计算：

CALCULATION :

SELECT RIGHT('0'+CAST((@EndingTime-@StartingTime)/60 AS VARCHAR),2)+':'+CAST((@EndingTime-@StartingTime)%60 AS VARCHAR) AS Result

另一种方式：

您可能希望使用 TIME 数据类型，但它也将保持占秒数。在您的情况下，您可以将零作为秒的值传递。

请参阅此链接： time（Transact-SQL） [ ^ ]

并且在显示数据时，您可以使用 -

Another way:
You may want to use TIME datatype, but it will keep account for seconds also. In your case you can pass Zero as value for seconds.
Refer this link: time (Transact-SQL)[^]
and while showing data, you can use-

SELECT SUBSTRING( CONVERT(VARCHAR, YourField,108),1,5)

计算：

CALCULATION :

SELECT RIGHT('0'+CAST(DATEDIFF(MINUTE,@StartingTime,@EndingTime)/60 AS VARCHAR),2)+':'+CAST(DATEDIFF(MINUTE,@StartingTime,@EndingTime)%60 AS VARCHAR) AS RESULT  

希望，它有助于：）

Hope, it helps :)

这篇关于如何在SQL SERVER 2008中只计算时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！