SQL Server 2005中如何比较两个日期找出时差，日期操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9521434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare two dates to find time difference in SQL Server 2005, date manipulation
提问by some_bloody_fool
I have two columns:
我有两列：
job_start job_end
2011-11-02 12:20:37.247 2011-11-02 13:35:14.613
How would it be possible using T-SQL to find the raw amount of time that has passed between when the job started and when the job ended?
如何使用 T-SQL 查找作业开始和作业结束之间经过的原始时间量？
I tried this:
我试过这个：
select (job_end - job_start) from tableA
but ended up with this:
但最终是这样的：
1900-01-01 01:14:37.367
回答by James Hill
Take a look at the DateDiff()function.
看一下DateDiff()功能。
-- Syntax
-- DATEDIFF ( datepart , startdate , enddate )
-- Example usage
SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff
SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff
SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff
SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff
SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff
...
You can see it in action / play with it here
回答by Vince Pergolizzi
You can use the DATEDIFFfunction to get the difference in minutes, seconds, days etc.
您可以使用DATEDIFF函数获取分钟、秒、天等的差异。
SELECT DATEDIFF(MINUTE,job_start,job_end)
MINUTE obviously returns the difference in minutes, you can also use DAY, HOUR, SECOND, YEAR (see the books online link for the full list).
MINUTE 显然返回以分钟为单位的差异，您也可以使用 DAY、HOUR、SECOND、YEAR（完整列表请参见在线书籍链接）。
If you want to get fancy you can show this differently for example 75 minutes could be displayed like this: 01:15:00:0
如果你想花哨，你可以用不同的方式显示，例如 75 分钟可以这样显示：01:15:00:0
Here is the code to do that for both SQL Server 2005 and 2008
这是为 SQL Server 2005 和 2008 执行此操作的代码
-- SQL Server 2005
SELECT CONVERT(VARCHAR(10),DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000'),114)
-- SQL Server 2008
SELECT CAST(DATEADD(MINUTE,DATEDIFF(MINUTE,job_start,job_end),'2011-01-01 00:00:00.000') AS TIME)
回答by Ric
Cast the result as TIMEand the result will be in time format for duration of the interval.
将结果转换为TIME，结果将在时间间隔内采用时间格式。
select CAST(job_end - job_start) AS TIME(0)) from tableA
回答by Sahan Samaranayaka
I think you need the time gap between job_start & job_end.
我认为您需要job_start 和 job_end之间的时间间隔。
Try this...
尝试这个...
select SUBSTRING(CONVERT(VARCHAR(20),(job_end - job_start),120),12,8) from tableA
I ended up with this.
我结束了这个。
01:14:37
回答by user6474160
Declare the Start and End date
DECLARE @SDATE AS DATETIME
声明开始和结束日期
DECLARE @SDATE AS DATETIME
TART_DATE AS DATETIME
DECLARE @END_-- Set Start and End date
SET @START_DATE = GETDATE()
SET @END_DATE = DATEADD(SECOND, 3910, GETDATE())
-- Get the Result in HH:MI:SS:MMM(24H) format
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
-- 以 HH:MI:SS:MMM(24H) 格式获取结果
SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
回答by Nandhakumar S
Below code gives in hh:mm format.
下面的代码以 hh:mm 格式给出。
select RIGHT(LEFT(job_end- job_start,17),5)
选择右（左（job_end-job_start，17），5）
回答by Kasim Husaini
I used following logic and it worked for me like marvel:
我使用了以下逻辑，它像奇迹一样对我有用：
CONVERT(TIME, DATEADD(MINUTE, DATEDIFF(MINUTE, AP.Time_IN, AP.Time_OUT), 0))
回答by rchacko
If you trying to get worked hours with some accuracy, try this (tested in SQL Server 2016)
如果您尝试以某种准确度获得工作时间，请尝试此操作（在 SQL Server 2016 中测试）
SELECT DATEDIFF(MINUTE,job_start, job_end)/60.00;
Various DATEDIFF functionalities are:
各种 DATEDIFF 功能是：
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
Ref: https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
参考：https: //docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view =sql-server- 2017
回答by tapos ghosh
If your database StartTime = 07:00:00and endtime = 14:00:00, and both are time type. Your query to get the time difference would be:
如果您的数据库StartTime = 07:00:00和 endtime = 14:00:00，并且都是时间类型。您获取时差的查询是：
SELECT TIMEDIFF(Time(endtime ), Time(StartTime )) from tbl_name
If your database startDate = 2014-07-20 07:00:00and endtime = 2014-07-20 23:00:00, you can also use this query.
如果您的数据库 startDate =2014-07-20 07:00:00和 endtime = 2014-07-20 23:00:00，您也可以使用此查询。
回答by Vikash Rakshit
Try this in Sql Server
在 Sql Server 中试试这个
SELECT
start_date as firstdate,end_date as seconddate
,cast(datediff(MI,start_date,end_date)as decimal(10,3)) as minutediff
,cast(cast(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60) as int ) as varchar(10)) + ' ' + 'Days' + ' '
+ cast(cast((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60) -
floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)) ) * 24 as int) as varchar(10)) + ':'
+ cast( cast(((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)
- floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)))*24
-
cast(floor((cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)
- floor(cast(datediff(MI,start_date,end_date)as decimal(10,3)) / (24*60)))*24) as decimal)) * 60 as int) as varchar(10))
FROM [AdventureWorks2012].dbo.learndate
