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.
| LogID | SessionDuration |
|---|---|
| 1001 | LogOutOn – 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;
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.
2 comments
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’)
Thank you @Martin for reading and providing valuable input. We appreciate.