Menu
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 
460
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".
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
173
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
