How to compare two dates to find time difference in SQL Server 2005, date manipulation
SqlSql Server-2005DateDatediffSql Problem Overview
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?
I tried this:
select (job_end - job_start) from tableA
but ended up with this:
1900-01-01 01:14:37.367
Sql Solutions
Solution 1 - Sql
Take a look at the DateDiff()
function.
-- 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
Solution 2 - Sql
You can use the DATEDIFF function to get the difference in minutes, seconds, days etc.
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).
If you want to get fancy you can show this differently for example 75 minutes could be displayed like this: 01:15:00:0
Here is the code to do that for both SQL Server 2005 and 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)
Solution 3 - Sql
Cast the result as TIME
and the result will be in time format for duration of the interval.
select CAST(job_end - job_start) AS TIME(0)) from tableA
Solution 4 - Sql
I think you need the time gap between 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
Solution 5 - Sql
Declare the Start and End date
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
Solution 6 - Sql
Take a look at DATEDIFF, this should be what you're looking for. It takes the two dates you're comparing, and the date unit you want the difference in (days, months, seconds...)
Solution 7 - Sql
If your database StartTime = 07:00:00
and endtime = 14:00:00
, and both are time type. Your query to get the time difference would be:
SELECT TIMEDIFF(Time(endtime ), Time(StartTime )) from tbl_name
If your database startDate = 2014-07-20 07:00:00
and endtime = 2014-07-20 23:00:00
, you can also use this query.
Solution 8 - Sql
Try this in 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
Solution 9 - Sql
Below code gives in hh:mm format.
select RIGHT(LEFT(job_end- job_start,17),5)
Solution 10 - Sql
I used following logic and it worked for me like marvel:
CONVERT(TIME, DATEADD(MINUTE, DATEDIFF(MINUTE, AP.Time_IN, AP.Time_OUT), 0))
Solution 11 - Sql
If you trying to get worked hours with some accuracy, try this (tested in SQL Server 2016)
SELECT DATEDIFF(MINUTE,job_start, job_end)/60.00;
Various DATEDIFF functionalities are:
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