sql server 2005将时间转换为分钟[重复]

This question already has an answer here:

这个问题在这里已有答案:

datetime to totalminute in sql 3 answers

在sql 3答案中的datetime to totalminute

I have a few date time functions within my database, but I need to add one that takes the time portion of a datetime field and then converts the time into minutes

我在我的数据库中有一些日期时间函数,但我需要添加一个占用日期时间字段的时间部分,然后将时间转换为分钟

I have a function that get the minutes between two times, but not just the minutes of a single time.

我有一个函数可以获得两次之间的分钟,但不仅仅是一次的分钟。

 ALTER FUNCTION [dbo].[fn_MinutesBetween]
 ( @fStart datetime, @fEnd datetime )
 RETURNS int
 AS
 BEGIN
 RETURN DateDiff(minute, @fStart, @fEnd)

and another one that gets just the time portion

另一个只得到时间部分

 ALTER function [dbo].[fn_ReturnTimeOnly]
 (@DateTime smallDateTime)
 returns nvarchar(50)
as
begin
  Return substring(cast(@DateTime as varchar),12,len(@DateTime))

end

How can I just get the minutes of the time. Like 1:00 am would be 60, 2:00 am would be 120 12:00 pm would be 720 etc.

我怎样才能得到时间的分钟。比如凌晨1点就是60点,凌晨2点就是120点12点就是720点等。

Thanks

3 个解决方案

#1

I was given a link in comments to datetime to totalminute and used that to come up with a solution.

我在评论中给了一个关于datetime的评论链接,并用它来提出一个解决方案。

ALTER FUNCTION [dbo].[fn_ReturnMinutesOnly]
  ( @dt smalldatetime )

RETURNS int
AS
BEGIN
  RETURN DATEDIFF(MINUTE, DATEADD(DAY, DATEDIFF(DAY, 0, @dt), 0), @dt)
END

#2

Get the number of hours, cast to int, multiply by 60, get the number of mins, cast to int, add these two.

获取小时数,转换为int,乘以60,得到分钟数,转换为int,添加这两个。

ALTER function [dbo].[fn_ReturnMinutesOnly]
 (@DateTime smallDateTime)
 returns INT
as
begin
        Return
            cast(substring(cast(@DateTime as varchar),13,2) as INT) * 60 +
            cast(substring(cast(@DateTime as varchar),16,2) as INT)
end

#3

Casting to string is expensive (I talk about the opposite scenario here, but the concept is the same). Try this instead:

投射到弦是昂贵的(我在这里谈论相反的情况,但概念是相同的)。试试这个:

DECLARE @DateTime DATETIME;
SET @DateTime = GETDATE();

SELECT DATEDIFF(MINUTE, DATEDIFF(DAY, 0, @DateTime), @DateTime);

In your function, this would be:

在您的函数中,这将是:

ALTER function [dbo].[fn_ReturnTimeOnly]
(@DateTime smallDateTime)
returns INT
as
begin
  Return (SELECT DATEDIFF(MINUTE, DATEDIFF(DAY, 0, @DateTime), @DateTime));
end

#1