如何在SQL Server中编写C＃的日期时间差异逻辑？

I have this logic in my C# code which gives me time difference between two times

我在C#代码中有这个逻辑,这给了我两次之间的时差

Example inputs are: '12:00 AM' - '11:15 AM' gives 45 minutes.

示例输入为:'12:00 AM' - '11:15 AM'给出45分钟。

DateTime startTime = Convert.ToDateTime(startTimeHH + ":" + startTimeMM + " " + startTimeAMPM);
DateTime endTime = Convert.ToDateTime(endTimeHH + ":" + endTimeMM + " " + endTimeAMPM);
DateTime breakTime = Convert.ToDateTime(breakTimeHH + ":" + breakTimeMM);

TimeSpan hours = endTime.Subtract(startTime);
hours = hours.Subtract(breakTime.TimeOfDay);

I referred some MSDN docs datetime functions in SQL server but couldn't find these functions which can give me time from input as 12:00 PM

我在SQL服务器中引用了一些MSDN docs日期时间函数,但找不到这些函数,这些函数可以给我输入时间为12:00 PM

However now I need to move this piece now into SQL server. How do I write it?

但是现在我需要将此部分移动到SQL服务器中。我怎么写呢?

Example inputs are: '12:00 AM' - '11:15 AM' won't return 45 it will return 75 (it contains 15 as 25)

示例输入为:'12:00 AM' - '11:15 AM'将不返回45它将返回75(它包含15作为25)

4 个解决方案

#1

Here is one way to get what looks like a .NET TimeSpan:

这是一种获得.NET TimeSpan的方法:

declare @startTimeHH char(2) = '10',
        @startTimeMM char(2) = '30',
        @startTimeAMPM char(2) = 'AM',
        @endTimeHH char(2) = '12',
        @endTimeMM char(2) = '00',
        @endTimeAMPM char(2) = 'PM',
        @breakTimeHH char(2) = '01',
        @breakTimeMM char(2) = '15',
        @startTime DateTime, 
        @endTime DateTime, 
        @breakTime DateTime, 
        @result Time

set @startTime = cast ((@startTimeHH + ':' + @startTimeMM + ' ' + @startTimeAMPM) as Time);
set @endTime = cast ((@endTimeHH + ':' + @endTimeMM + ' ' + @endTimeAMPM) as Time);
set @breakTime = cast ((@breakTimeHH + ':' + @breakTimeMM) as Time);

set @result = @endTime - @startTime - @breakTime
select cast(@result as CHAR(8))

The result is 00:15:00

结果是00:15:00

#2

CREATE TABLE [dbo].#events (            
    [StartTime] time NULL,            
    [EndTime] time NULL )            

INSERT INTO #events            
VALUES            
('08:00', '08:30'),            
('08:30', '08:00'),            
('09:00', '10:00'),            
('15:00', '16:30')  

select  convert(time, dateadd(minute, datediff(minute, StartTime, EndTime), 0))  
From #events

drop table #events

ResultSet

00:30:00.0000000
23:30:00.0000000
01:00:00.0000000
01:30:00.0000000

00:30:00.0000000 23:30:00.0000000 01:00:00.0000000 01:30:00.0000000

#3

Use DateDiff function

使用DateDiff函数

DatePart will be handy, too

DatePart也很方便

#4

Here is some SQL that returns '45' (minutes)

这是一些返回'45'(分钟)的SQL

DECLARE @Start DATETIME, @END DATETIME
SET @Start = '2009-05-02 10:00:50'
set @End = '2009-05-02 10:45:50'

    -- FYI
SELECT @start, @end

    -- returns 45
SELECT DateDiff(mi, @start, @end)