How to calculate date difference in T-SQL with output in time format

It has been pretty long time since I blogged and also I regret a lot of not being able to spend sufficient time for my blogs.Hopefully I will be able to blog more frequently very soon.

Recently one of my customer had a requirement to calculate the duration between a column Starttime and Endtime while querying a table. The requirement sounds simple and not a big deal since we have inbuilt DATEDIFF function which calculates difference between the 2 datetime but the catch was we wanted to represent the duration in time format and DATEDIFF has a return type of int wherein it can display the output either in hours or mins or seconds but not in the form of hh:mm:ss

Let me illustrate this with an example

Consider a table as shown below

CREATE TABLE [dbo].[Events]
(
[EventID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO   --Insert some dummy values in the table

insert into dbo.events values
('2007-01-01 06:34:12.000','2007-01-01 12:45:34.000'),
('2007-01-02 09:23:08.000','2007-01-02 17:05:37.000'),
('2007-01-03 16:34:12.000','2007-01-03 16:55:18.000'),
('2007-01-04 11:02:00.000','2007-01-04 14:53:21.000'),
('2007-01-05 07:52:55.000','2007-01-05 09:08:48.000'),
('2007-01-06 19:59:11.000','2007-01-07 01:23:11.000'),
('2007-01-07 03:12:23.000','2007-01-07 20:02:25.000')

If I use the following query

select *,DATEDIFF(ss,StartDate,EndDate) [Duration] from dbo.Events

Output is of the form

EventID     StartDate               EndDate                                                     Duration
———– ———————– ———————–                                 ———–
1           2007-01-01 06:34:12.000 2007-01-01 12:45:34.000                  22282
2           2007-01-02 09:23:08.000 2007-01-02 17:05:37.000                27749
3           2007-01-03 16:34:12.000 2007-01-03 16:55:18.000                   1266
4           2007-01-04 11:02:00.000 2007-01-04 14:53:21.000                  13881

However we wanted the output to be in the format of hh:mi:ss. In SQL 2008 this can be easily achieved since we have time data-type introduced and hence to get the desired out we can use the following query.

select *,CAST((EndDate-StartDate) as time(0)) 
[Duration] from dbo.Events

EventID     StartDate               EndDate                                                                     Duration
———– ———————– ———————– —–                                       ———–
1           2007-01-01 06:34:12.000 2007-01-01 12:45:34.000                              06:11:22
2           2007-01-02 09:23:08.000 2007-01-02 17:05:37.000                            07:42:29
3           2007-01-03 16:34:12.000 2007-01-03 16:55:18.000                              00:21:06
4           2007-01-04 11:02:00.000 2007-01-04 14:53:21.000                              03:51:21
5           2007-01-05 07:52:55.000 2007-01-05 09:08:48.000                             01:15:53
6           2007-01-06 19:59:11.000 2007-01-07 01:23:11.000                                 05:24:00

But if we try the same in SQL 2005 wherein we had only datetime data-type in the output we get annoying Date part as 1900-01-01

EventID     StartDate               EndDate                                                                            Duration
———– ———————– ———————–                                                       ————–
1        2007-01-01 06:34:12.000 2007-01-01 12:45:34.000                                    1900-01-01 06:11:22.000
2       2007-01-02 09:23:08.000 2007-01-02 17:05:37.000                                   1900-01-01 07:42:29.000
3       2007-01-03 16:34:12.000 2007-01-03 16:55:18.000                                     1900-01-01 00:21:06.000
4       2007-01-04 11:02:00.000 2007-01-04 14:53:21.000                                    1900-01-01 03:51:21.000
5       2007-01-05 07:52:55.000 2007-01-05 09:08:48.000                                  1900-01-01 01:15:53.000

So in order to achieve the same in SQL 2005 we need to write a user defined function to achieve this.

Method 1: (Preferred Method suggested by mjoksa)

This would the preferred method to compute date difference and display the output in time format in SQL 2005.

select *,right(convert(varchar, cast(EndDate - StartDate as datetime), 121), 12) [Duration] from dbo.Events

EventID StartDate EndDate Duration
———– ———————– ———————– ———-
1 2007-01-01 06:34:12.000 2007-01-01 12:45:34.000 06:11:22
2 2007-01-02 09:23:08.000 2007-01-02 17:05:37.000 07:42:29
3 2007-01-03 16:34:12.000 2007-01-03 16:55:18.000 00:21:06
4 2007-01-04 11:02:00.000 2007-01-04 14:53:21.000 03:51:21
5 2007-01-05 07:52:55.000 2007-01-05 09:08:48.000 01:15:53
6 2007-01-06 19:59:11.000 2007-01-07 01:23:11.000 05:24:00

Method 2:

Since I wasn’t aware of Method 1 at the time when I was writing this post, I wrote the following UDF to achieve the same result.

create function [dbo].[gettime](@duration int) returns varchar(10)
begin
DECLARE @mm int;
DECLARE @hh int;
DECLARE @ss int;
DECLARE @minute varchar(2);
DECLARE @hour varchar(2);
DECLARE @second varchar(2);
DECLARE @time varchar(10);

SET @mm=0;
SET @hh=0;
SET @ss = @duration;

if (@duration > 60)
Begin
SELECT @mm=@duration/60;
SELECT @ss=@duration%60;
if(@mm > 60)
Begin
SELECT @hh=@mm/60;
SET @mm=@mm%60;
end
end

if(@hh<10)
SET @hour='0'+cast(@hh as varchar(2))
else
SET @hour = cast(@hh as varchar(2))

if(@mm<10)
SET @minute='0'+cast(@mm as varchar(2))
else
SET @minute = cast(@mm as varchar(2))

if(@ss<10)
SET @second ='0'+cast(@ss as varchar(2))
else
SET @second = @ss

SET @time=@hour+':'+@minute+':'+@second;
return @time
End
GO

So now when we fire the following query we get the desired output

select *,dbo.gettime(DATEDIFF(ss,StartDate,EndDate)) [Duration] from dbo.Events

EventID     StartDate               EndDate                                                             Duration
———– ———————– ———————–                                       ———-
1           2007-01-01 06:34:12.000 2007-01-01 12:45:34.000                     06:11:22
2           2007-01-02 09:23:08.000 2007-01-02 17:05:37.000                   07:42:29
3           2007-01-03 16:34:12.000 2007-01-03 16:55:18.000                     00:21:06
4           2007-01-04 11:02:00.000 2007-01-04 14:53:21.000                    03:51:21
5           2007-01-05 07:52:55.000 2007-01-05 09:08:48.000                   01:15:53
6           2007-01-06 19:59:11.000 2007-01-07 01:23:11.000                      05:24:00

Hope this helps

Parikshit Savjani
Premier Field Engineer,Microsoft

5 comments

  1. You do not need to write UDF to get time value in SQL2005.
    You can use something like this
    right(convert(varchar, cast(@EndDate – @StartDate as datetime), 121), 12)

  2. Thanks mjoksa….I didn’t knew that…I will modify the post and add your technique as Method 1 since it can save time for many of our users…..Hope you don’t mind!!!

  3. Hi,
    I tried above query it says that there is incorrect syntax near the ‘-‘

    can u please help me

    thanks in advance.

  4. Can you help me modify your Method 1 to provide number of days as well?

    Ex. Start Date: 2012-04-01 19:43
    End Date: 2012-05-13 04:52

  5. Hi Laura, If you need No. of Days, you can directly use the inbuilt Datediff function available with SQL.

    This is how the query would like

    select *,DateDiff(dd,StartDate,EndDate) [No. of days] from dbo.Events

Leave a comment

Your email address will not be published. Required fields are marked *