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
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.


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

 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.

 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.

  DATEDIFF(hh,LogInOn,LogOutOn) AS LogInDuration 

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.