Calculate Time difference between start DateTime and End DateTime in SQL Server

Ruben123...

SOLVED CLOSED ACTIVE ANSWERED
User: Ruben12345
Posted: on May 29, 2017 03:42 AM
Forum: SQL Server
Answer: 2
Views: 5413

i have join view table where 1 table contain begin date amnd end date. i want calculate both of them as duratiowith format dd:hh:mm (2d:3h:34m)

SELECT sa.id, sa.shipment_no, v.vehicle_name, m.member_name, sa.begin_date_time, sa.create_date, sa.member_id, sa.partner_id, sa.vehicle_id, sa.end_date_time, sa.active
FROM dbo.shipment_assignment AS sa 
INNER JOIN dbo.vehicles AS v ON v.vehicle_id = sa.vehicle_id INNER JOIN
dbo.members AS m ON sa.partner_id = m.member_i

Download FREE API for Word, Excel and PDF in ASP.Net

AnandM

0 3

Reply
ANSWER
User: AnandM
Replied: on May 29, 2017 08:18 AM
Modified: on May 29, 2017 08:21 AM
Report

Hi Ruben12345,

Refer below SQL query

SQL

DECLARE @TABLEA AS TABLE (ID INT ,shipment_no INT,vehicle_name VARCHAR(50), member_name VARCHAR(50),begin_date_time DATETIME,create_date DATETIME,member_id INT,partner_id INT,vehicle_id INT,end_date_time DATETIME,active INT)
INSERT INTO @TABLEA VALUES(1,12233,'B1278HU','Unilever',' 2017-05-10 13:09:00','2017-05-09',3344552,4555433,1442344,'2017-05-12 12:00:00',0)
INSERT INTO @TABLEA VALUES(13,122433,'B12sx78HU','Unilexver',' 2017-05-29 13:09:00','2017-05-09',3344552,4555433,1442344,GETDATE(),0)

SELECT *,CONVERT(VARCHAR(5),DateDiff(dd, begin_date_time, end_date_time)) + ' Days '
	   + CONVERT(VARCHAR(5),DateDiff(hh, begin_date_time, end_date_time) % 24) + ' hours '
	   + CONVERT(VARCHAR(5),DateDiff(mi, begin_date_time, end_date_time) % 60) + ' minutes '
	   + CONVERT(VARCHAR(5),(DATEDIFF(S, begin_date_time, end_date_time)%60))+' seconds' As TotalDiff
FROM @TABLEA

Output

ID	shipment_no	vehicle_name	member_name	begin_date_time	create_date	member_id	partner_id	vehicle_id	end_date_time	active	TotalDiff
1	12233	B1278HU	Unilever	09:00.0	00:00.0	3344552	4555433	1442344	00:00.0	0	2 Days 23 hours 51 minutes 0 seconds
13	122433	B12sx78HU	Unilexver	09:00.0	00:00.0	3344552	4555433	1442344	40:20.3	0	0 Days 6 hours 31 minutes 20 seconds

AnandM

0 5

Reply
ANSWER
User: AnandM
Replied: on May 29, 2017 08:43 AM
Report

Ruben12345 says:

SELECT sa.id, sa.shipment_no, v.vehicle_name, m.member_name, sa.begin_date_time, sa.create_date, sa.member_id, sa.partner_id, sa.vehicle_id, sa.end_date_time, sa.active
FROM dbo.shipment_assignment AS sa INNER JOIN
 dbo.vehicles AS v ON v.vehicle_id = sa.vehicle_id INNER JOIN
 dbo.members AS m ON sa.partner_id = m.member_id

replace above code with below

SELECT sa.id
	  ,sa.shipment_no
	  ,v.vehicle_name
	  ,m.member_name
	  ,sa.begin_date_time
	  ,sa.create_date
	  ,sa.member_id
	  ,sa.partner_id
	  ,sa.vehicle_id
	  ,sa.end_date_time
	  ,sa.active
	  ,  CONVERT(VARCHAR(5),DateDiff(dd, sa.begin_date_time, sa.end_date_time)) + ' Days '
	   + CONVERT(VARCHAR(5),DateDiff(hh, sa.begin_date_time, sa.end_date_time) % 24) + ' hours '
	   + CONVERT(VARCHAR(5),DateDiff(mi, sa.begin_date_time, sa.end_date_time) % 60) + ' minutes '
	   + CONVERT(VARCHAR(5),(DATEDIFF(S, sa.begin_date_time, sa.end_date_time)%60))+' seconds' As TotalDiff
FROM dbo.shipment_assignment AS sa INNER JOIN
dbo.vehicles AS v ON v.vehicle_id = sa.vehicle_id INNER JOIN
dbo.members AS m ON sa.partner_id = m.member_id

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate Time difference between start DateTime and End DateTime in SQL Server

Company

Explore

Follow us

Contact Us