[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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!!!!
0
erin027
Asked:
erin027
1 Solution
 
BinuthCommented:
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now