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
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)
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!!!
Hi,
I tried above query it says that there is incorrect syntax near the ‘-‘
can u please help me
thanks in advance.
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
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