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
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.
|1001||LogOutOn – LogInOn (in hours)|
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;
We have also written many articles on SQL topic earlier.
You can read all posts on Microsoft SQL at SQL Archive.