Introduction
First of all, majority of the formatting should not be done in database queries. Instead, the database should return values and the values should then be formatted on the calling side taking different kinds of user settings, such as number formatting, language, etc. into account.
Having that said, when a difference between two dates is calculated, it sometimes makes sense to calculate the different parts of the difference on the database side and perhaps even format the values.
Creating the Test Data
First of all, we need a place for the test data so let's create a temporary table for that:
CREATE TABLE #TextualDateDiffTest (
StartDate datetime not null,
EndDate datetime not null
);
And then insert some data into it.
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
CONVERT(datetime, '2014-09-18 20:30:20', 120) );
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
CONVERT(datetime, '2014-09-19 20:30:20', 120) );
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
CONVERT(datetime, '2014-09-19 12:20:20', 120) );
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
CONVERT(datetime, '2014-10-19 20:55:44', 120) );
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
CONVERT(datetime, '2014-10-05 16:22:15', 120) );
INSERT INTO #TextualDateDiffTest (StartDate, EndDate)
VALUES ( CONVERT(datetime, '2014-09-18 19:30:00', 120),
GETDATE() );
DATEDIFF, Why Not?
There are a ton of examples how to calculate the difference for two datetime values using DATEDIFF function. So, why would a new tip be needed. DATEDIFF calculates the whole difference between two dates. In other words, if you choose the interval to be minutes a difference is expressed in minutes even if the difference is greater than a single hour. This introduces complexity in the calculation since in each date part, the previous date part value needs to be taken into account.
Let's have a look at this using an example:
SELECT a.StartDate,
a.EndDate,
DATEDIFF(YEAR, a.StartDate, a.EndDate) AS Years,
DATEDIFF(MONTH, a.StartDate, a.EndDate) AS Months,
DATEDIFF(DAY, a.StartDate, a.EndDate) AS Days,
DATEDIFF(HOUR, a.StartDate, a.EndDate) AS Hours,
DATEDIFF(MINUTE, a.StartDate, a.EndDate) AS Minutes,
DATEDIFF(SECOND, a.StartDate, a.EndDate) AS Seconds
FROM #TextualDateDiffTest a;
The query above returns the following data:
StartDate EndDate Years Months Days Hours Minutes Seconds
----------------------- ----------------------- ----- ------ ---- ----- ------- -------
2014-09-18 19:30:00.000 2014-09-18 20:30:20.000 0 0 0 1 60 3620
2014-09-18 19:30:00.000 2014-09-19 20:30:20.000 0 0 1 25 1500 90020
2014-09-18 19:30:00.000 2014-09-19 12:20:20.000 0 0 1 17 1010 60620
2014-09-18 19:30:00.000 2014-10-19 20:55:44.000 0 1 31 745 44725 2683544
2014-09-18 19:30:00.000 2014-10-05 16:22:15.000 0 1 17 405 24292 1457535
2014-09-18 19:30:00.000 2015-09-19 08:16:33.600 1 12 366 8773 526366 31581993
It's easy to see that on the first line, hours is correct but minutes contains the hour difference and seconds contain them both. So in order to get the desired result, more calculation would be needed...
Using Subtraction
There's a much more easier way to calculate the parts of the difference. Basically, all you need to do is to subtract the start date from the end date. Like this:
SELECT a.StartDate,
a.EndDate,
a.EndDate - a.StartDate AS Difference
FROM #TextualDateDiffTest a;
The result is:
StartDate EndDate Difference
----------------------- ----------------------- -----------------------
2014-09-18 19:30:00.000 2014-09-18 20:30:20.000 1900-01-01 01:00:20.000
2014-09-18 19:30:00.000 2014-09-19 20:30:20.000 1900-01-02 01:00:20.000
2014-09-18 19:30:00.000 2014-09-19 12:20:20.000 1900-01-01 16:50:20.000
2014-09-18 19:30:00.000 2014-10-19 20:55:44.000 1900-02-01 01:25:44.000
2014-09-18 19:30:00.000 2014-10-05 16:22:15.000 1900-01-17 20:52:15.000
2014-09-18 19:30:00.000 2015-09-18 21:06:54.833 1901-01-01 01:36:54.833
Now if we put the subtraction into the original statement, splitting the parts of the date, the statement could look like this:
SELECT a.StartDate,
a.EndDate,
DATEPART(YEAR, a.EndDate - a.StartDate) AS Years,
DATEPART(MONTH, a.EndDate - a.StartDate) AS Months,
DATEPART(DAY, a.EndDate - a.StartDate) AS Days,
DATEPART(HOUR, a.EndDate - a.StartDate) AS Hours,
DATEPART(MINUTE, a.EndDate - a.StartDate) AS Minutes,
DATEPART(SECOND, a.EndDate - a.StartDate) AS Seconds
FROM #TextualDateDiffTest a;
And the result would be:
StartDate EndDate Years Months Days Hours Minutes Seconds
----------------------- ----------------------- ----- ------ ---- ----- ------- -------
2014-09-18 19:30:00.000 2014-09-18 20:30:20.000 1900 1 1 1 0 20
2014-09-18 19:30:00.000 2014-09-19 20:30:20.000 1900 1 2 1 0 20
2014-09-18 19:30:00.000 2014-09-19 12:20:20.000 1900 1 1 16 50 20
2014-09-18 19:30:00.000 2014-10-19 20:55:44.000 1900 2 1 1 25 44
2014-09-18 19:30:00.000 2014-10-05 16:22:15.000 1900 1 17 20 52 15
2014-09-18 19:30:00.000 2015-09-19 08:16:33.600 1901 1 1 12 46 33
Not quite what we wanted yet... Now the difference is correct but the date portion contains the date where SQL Server starts to calculate date values. The initial date is 1900-01-01 so in order to get the correct results, we need to subtract corresponding years, months, and minutes. Like this:
SELECT a.StartDate,
a.EndDate,
DATEPART(YEAR, a.EndDate - a.StartDate) - 1900 AS Years,
DATEPART(MONTH, a.EndDate - a.StartDate) - 1 AS Months,
DATEPART(DAY, a.EndDate - a.StartDate) - 1 AS Days,
DATEPART(HOUR, a.EndDate - a.StartDate) AS Hours,
DATEPART(MINUTE, a.EndDate - a.StartDate) AS Minutes,
DATEPART(SECOND, a.EndDate - a.StartDate) AS Seconds
FROM #TextualDateDiffTest a;
Now the result looks much better:
StartDate EndDate Years Months Days Hours Minutes Seconds
----------------------- ----------------------- ----- ------ ---- ----- ------- -------
2014-09-18 19:30:00.000 2014-09-18 20:30:20.000 0 0 0 1 0 20
2014-09-18 19:30:00.000 2014-09-19 20:30:20.000 0 0 1 1 0 20
2014-09-18 19:30:00.000 2014-09-19 12:20:20.000 0 0 0 16 50 20
2014-09-18 19:30:00.000 2014-10-19 20:55:44.000 0 1 0 1 25 44
2014-09-18 19:30:00.000 2014-10-05 16:22:15.000 0 0 16 20 52 15
2014-09-18 19:30:00.000 2015-09-19 08:16:33.600 1 0 0 12 46 33
The good thing is that the SQL statement contains no extra calculation and is very understandable. This would be easy to use as a result set for further formatting regardless of the programming language on the client side.
Formatting the Result
Now since the data is correct, it's easy to add formatting to the statement, if needed. As an example, let's extract the parts and put an explaining text in the end to indicate the meaning of the number, like this:
SELECT CAST( DATEPART(YEAR, a.EndDate - a.StartDate) - 1900 AS nvarchar(100)) + ' Years '
+ CAST( DATEPART(MONTH, a.EndDate - a.StartDate) - 1 AS nvarchar(100)) + ' Months '
+ CAST( DATEPART(DAY, a.EndDate - a.StartDate) - 1 AS nvarchar(100)) + ' Days '
+ CAST( DATEPART(HOUR, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Hours '
+ CAST( DATEPART(MINUTE, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Minutes '
+ CAST( DATEPART(SECOND, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Seconds' AS Difference
FROM #TextualDateDiffTest a;
The result would be:
Difference
-------------------------------------------------------------------
0 Years 0 Months 0 Days 1 Hours 0 Minutes 20 Seconds
0 Years 0 Months 1 Days 1 Hours 0 Minutes 20 Seconds
0 Years 0 Months 0 Days 16 Hours 50 Minutes 20 Seconds
0 Years 1 Months 0 Days 1 Hours 25 Minutes 44 Seconds
0 Years 0 Months 16 Days 20 Hours 52 Minutes 15 Seconds
1 Years 0 Months 0 Days 12 Hours 46 Minutes 33 Seconds
If you want to go a bit further and remove the parts of the difference that are zero valued, consider the following example:
SELECT CASE
WHEN DATEPART(YEAR, a.EndDate - a.StartDate) > 1900
THEN CAST( DATEPART(YEAR, a.EndDate - a.StartDate) - 1900 AS nvarchar(100)) + ' Year '
ELSE ''
END
+ CASE
WHEN DATEPART(MONTH, a.EndDate - a.StartDate) > 1
THEN CAST( DATEPART(MONTH, a.EndDate - a.StartDate) - 1 AS nvarchar(100)) + ' Months '
ELSE ''
END
+ CASE
WHEN DATEPART(DAY, a.EndDate - a.StartDate) > 1
THEN CAST( DATEPART(DAY, a.EndDate - a.StartDate) - 1 AS nvarchar(100)) + ' Days '
ELSE ''
END
+ CASE
WHEN DATEPART(HOUR, a.EndDate - a.StartDate) > 0
THEN CAST( DATEPART(HOUR, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Hours '
ELSE ''
END
+ CASE
WHEN DATEPART(MINUTE, a.EndDate - a.StartDate) > 0
THEN CAST( DATEPART(MINUTE, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Minutes '
ELSE ''
END
+ CASE
WHEN DATEPART(SECOND, a.EndDate - a.StartDate) > 0
THEN CAST( DATEPART(SECOND, a.EndDate - a.StartDate) AS nvarchar(100)) + ' Seconds'
ELSE ''
END AS Difference
FROM #TextualDateDiffTest a;
And the result set would now be:
Difference
-------------------------------------
1 Hours 20 Seconds
1 Days 1 Hours 20 Seconds
16 Hours 50 Minutes 20 Seconds
1 Months 1 Hours 25 Minutes 44 Seconds
16 Days 20 Hours 52 Minutes 15 Seconds
1 Year 1 Hours 36 Minutes 54 Seconds
This looks like it could be in understandable format for the user.
References
Some references you may find useful:
History
- 18th September, 2015: Created
