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
This question is related to
sql
sql-server-2005
date
datediff
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
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
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
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.
Below code gives in hh:mm format.
select RIGHT(LEFT(job_end- job_start,17),5)
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)
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...)
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
I used following logic and it worked for me like marvel:
CONVERT(TIME, DATEADD(MINUTE, DATEDIFF(MINUTE, AP.Time_IN, AP.Time_OUT), 0))
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
Source: Stackoverflow.com