Display Total Days, Hours and Minutes between two Dates in SQL Server

sql server

← PrevNext →

Tools you can use

Let us assume, I have two dates with time like 2021-01-01 13:15PM and 2021-01-05 17:29PM and I want to display the total number of days along with Hours and Minutes in this format… Total Days | hh:mm:ss.

I’ll be more specific.

The two Dates with time,
2021-01-01 13:15PM and 2021-01-05 17:29PM

Output should be,
4 | 04:14:00

The SQL Query

DECLARE	@startdate datetime
DECLARE	@enddate datetime

SET @startdate = '2021-01-01 13:15PM'
SET @enddate = '2021-01-05 17:29PM'

SELECT DATEDIFF(DAY, @startdate, @enddate) AS 'Total Days',
    CONVERT(VARCHAR(8), DATEADD(S, DATEDIFF(S, @startdate, @enddate), '1900-1-1'), 8) AS 'hh:mm:ss'

Output image

DateDiff() function example

Now let me explain the query.

I have declared two variables, @startdate and @enddate of type datetime. The query shows the result in two columns, that is, Total Days and time in hh:mm:ss format.

The DATEDIFF() function in the query (the first column in the output), gets me the total number of days between the two dates. The function takes three parameters. See the syntax.

DATEDIFF(interval, date1, date2)

Here the parameter interval is DAY (in the above query) the remaining two parameters are start date and end date.

Which is:

SELECT DATEDIFF(DAY, @startdate, @enddate) AS 'Total Days'

Note: Instead of DAY you can use D for the first parameter like this...

SELECT DATEDIFF(D, @startdate, @enddate) AS 'Total Days'

👉 Also read: How to get the First and Last day of a given Month in SQL Server
Get the First and Last Day of a given Month in SQL Server

The 2nd column, which results in hh:mm:ss is important here. How did I get the result in this format? I’ll separate each function and explain.

The DATEDIFF() function: It will get the total seconds.

SELECT DATEDIFF(S, @startdate, @enddate) AS 'Total Seconds'

Which results in,

image

Get total seconds between two dates using DateDiff() function

Next, I’ll use the DATEADD() function to add a date and time interval to the total seconds.

SELECT DATEADD(S, DATEDIFF(S, @startdate, @enddate), '1900-1-1')

The 1900-1-1 is the date format. Which results in,

image

Using DateDiff() function with DateAdd() in SQL Server

Finally, I’ll convert the result to get an output in hh:mm:ss format.

SELECT SELECT CONVERT(VARCHAR(8), SELECT DATEADD(S, 
    SELECT DATEDIFF(S, SELECT @startdate, @enddate), SELECT '1900-1-1'), 8) AS SELECT 'hh:mm:ss'

That's it.

I hope you find this example useful. I have used the above query in one of my projects where I had to calculate the in and out time and “total leave” taken by employees in an organization.

The query here used three important built-in SQL Server functions and these are DATEDIFF(), DATEADD() and CONVERT().

Thanks for reading.☺

← PreviousNext →