MSSQL中两个时间相减的计算

Introduction

MSSQL is a powerful relational database management system that includes a wide range of features for storing, managing, and manipulating data. One of the fundamental operations in any database is calculating the difference between two dates or times. In this article, we will focus on how to subtract two time values in MSSQL.

Subtracting Times in MSSQL

Subtracting two time values in MSSQL is a straightforward process. We can use the Datediff() function to calculate the difference between two dates or times. The Datediff() function takes three arguments: the time interval to use for the calculation, the start time, and the end time.

Calculating the Difference Between Two Times in Seconds

Suppose we have two time values, StartTime and EndTime, and we want to find out the time difference in seconds. We can use the Datediff() function as follows:

DECLARE @StartTime DATETIME = '2022-07-23 12:30:00';
DECLARE @EndTime DATETIME = '2022-07-23 14:30:00';
SELECT DATEDIFF(SECOND, @StartTime, @EndTime) AS TimeDiffInSeconds;

In the above example, we first declared two variables, @StartTime and @EndTime, and assigned them the date and time values. Then we used the Datediff() function to calculate the difference between the two times in seconds. The output of the query will be:

Output:

The output indicates that the time difference between the start time and end time is 7200 seconds (2 hours).

Calculating the Difference Between Two Times in Minutes

Similarly, if we want to find out the time difference in minutes, we can use the Datediff() function and specify the minute interval. The following example demonstrates this:

DECLARE @StartTime DATETIME = '2022-07-23 12:30:00';
DECLARE @EndTime DATETIME = '2022-07-23 14:30:00';
SELECT DATEDIFF(MINUTE, @StartTime, @EndTime) AS TimeDiffInMinutes;

The output of the above query will be:

Output:

The output indicates that the time difference between the start time and end time is 120 minutes (2 hours).

Calculating the Difference Between Two Times in Hours

If we want to find out the time difference in hours, we can use the Datediff() function and specify the hour interval. The following example demonstrates this:

DECLARE @StartTime DATETIME = '2022-07-23 12:30:00';
DECLARE @EndTime DATETIME = '2022-07-23 14:30:00';
SELECT DATEDIFF(HOUR, @StartTime, @EndTime) AS TimeDiffInHours;

The output of the above query will be:

Output:

The output indicates that the time difference between the start time and end time is 2 hours.

Conclusion

Subtracting two time values in MSSQL is a simple process that can be accomplished using the Datediff() function. In this article, we have shown how to calculate the time difference between two times in seconds, minutes, and hours. By using these techniques, you can easily perform time-related calculations in your SQL queries.