MS SQL, Time difference

I am using MS-SQL 2005
Let say I have two table like below:

TableA                          TableB
----------                        -------------
ID_A                             ID_B
RegisterDate                ID_A
                                     AssignedDate

And I have use this query to select all the data from both tables:
Select registerdate,AssignedDate
From TableA a
Left Join Table B b ON b.ID_A = a.ID_A

So result came out well, but I wanted to include 'Duration Time' in the result. Example result will be something like this:

RegisterDate                   AssignedDate                  DurationTime
01/01/2008 01:00:00      01/01/2008 02:00:00         01:00:00

Can you experts help me!!!!
erin027Asked:
Who is Participating?
 
BinuthConnect With a Mentor Commented:
try this
SELECT
	A.registerdate,
	B.AssignedDate,
	CASE WHEN b.AssignedDate IS NULL THEN '00:00:00' 
		ELSE
			CAST(((DATEDIFF(ss,A.registerdate,B.AssignedDate)/3600)) AS VARCHAR) + ':' + CAST(((DATEDIFF(ss,A.registerdate,B.AssignedDate)%3600)/60) AS VARCHAR) + ':' + CAST((((DATEDIFF(ss,A.registerdate,B.AssignedDate)%3600)%60)%60) AS VARCHAR)
		END AS DurationTime
FROM TableA A
LEFT JOIN TableB B ON B.ID_A = A.ID_A

Open in new window

0
 
erin027Author Commented:
Thank you so much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.