Home ProgrammingSQL Get difference of two date times in hours in SQL – DATEDIFF

Get difference of two date times in hours in SQL – DATEDIFF

by John Bhatt
2487 views
SQL Query and Output for DATEDIFF function to calculate Hours between two dates

Today, we are back with a simple Microsoft SQL Server tip. If you use SQL Server as back-end in your application, you may need this at-least any time.

Situation

Few days back, when I was working for a client, they have their vehicle tracking data which was from from a manual source based on the date and timings mentioned in physical document.

They captured all data and converted to Microsoft Excel format but now they have a challenge.

They want to calculate the time taken by vehicle for each trip. We have to get hours by subtracting two dates. We took the help of SQL and ASP.NET because all that data was to be imported and planned for migration in their new ERP.

For the similar demonstration, we have created a simple demo. We have created a SQL table for storing sessions and added few records manually. Then applied the method we used to get the desired result.

Lets have a look at what we have and what we did. We used SQL Server 2016 with SQL Server Management Studio 2016.

Database Table Structure

CREATE TABLE SessionLog
(
 LogID int primary key identity(1000,1),
 UserID int,
 UserName nvarchar(50),
 SessionID nvarchar(100),
 UsersIP nvarchar(100),
 UsersMac nvarchar(500),
 LogInOn datetime default getdate(),
 UserAgent nvarchar(500),
 LogOutOn datetime,
 ReturnURL nvarchar(500),
);

Now, our desired output is like, we have to add the output in Select Statement and display to user.

LogIDSessionDuration
 1001LogOutOn – LogInOn (in hours)

SQL Command

We used a DATEDIFF  function to get our desired result. DATEDIFF function gives us the difference between given dates and also can convert that output to various other time formats like, Hours, Minutes, Seconds, Days etc.

DATEDIFF(hh, SessionLog.LogInOn, SessionLog.LogOutOn)

Description of the above line of code.

DATEDIFF  – Name of function.

hh  – First parameter which denotes the output should be in hh (hours) format.

LogInOn  – The column name which is to be subtracted.

LogOutOn  – The column name from which second parameter to subtract.

Complete command we have used to display record is like.

SELECT 
  LogID, 
  DATEDIFF(hh,LogInOn,LogOutOn) AS LogInDuration 
 FROM 
SessionLog;

SQL Output of DATEDIFF method

We have also written many articles on SQL topic earlier.

List All SQL Server Instances in Combo (Windows Form)

List All SQL Server Instances in Combo (Windows Form) – Part-2

Dynamic Meta Tags from SQL Database in MasterPage

You can read all posts on Microsoft SQL at SQL Archive.

In this article, we shared you a wonderful and useful SQL method DATEDIFF. Subscribe to our newsletters and our YouTube channel for getting new content whenever we publish something new.

Related Articles

2 comments

Martin Douglas May 23, 2020 - 3:30 AM

DATEDIFF has some very important caveats about boundaries. Consider the following approach when you want a timespan calculated with support for 24+ hour differences.

— Note that this may be at most 1 second off since DATEDIFF counts the number of seconds boundary crossings (and not seconds elapsed)
DECLARE @AgeInMinutes int = DATEDIFF(SECOND, ‘2020-05-14 14:17:53.9741769’, SYSUTCDATETIME())

— But good enough for determining a timespan as a utility value
SELECT TimeSpan =
FORMAT(@AgeInMinutes/(24*60*60), ‘\+#d ;””;””‘) +
FORMAT(@AgeInMinutes%(24*60*60)/(60*60), ’00’) +
FORMAT(@AgeInMinutes%(60*60)/(60), ‘\:00’) +
FORMAT(@AgeInMinutes%(60), ‘\:00’);

— Result example
> +7d 09:53:52

— Just to illustrate why one cannot simply stack a bunch of DATEDIFF results together… the result below is 1 (day boundary) which is undesired
SELECT DATEDIFF(DAY, ‘2020-05-13 23:00:00’, ‘2020-05-14 02:00:00’)

Reply
John Bhatt May 23, 2020 - 10:08 PM

Thank you @Martin for reading and providing valuable input. We appreciate.

Reply

Leave a Reply

Do NOT follow this link or you will be banned from the site!