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