Calculate date and time with decimal

Transact-SQL
1

Hi,

I have to calculate the difference of 2 dates and with time were the result has a decimal points. couldn't find any useful technique to get the desired result. any idea is very much appreciated. thank you.

below is sample data and desired result:


DECLARE	@Sample TABLE
	(
		receiptdate datetime,
		createddate datetime,
		endeddate datetime
	)

-- Populate sample data
INSERT	@Sample VALUES	('2016-07-03 15:59:38.000','2016-07-06 00:29:48.000','2016-07-20 05:02:26.000')
INSERT	@Sample VALUES	('2016-07-03 15:59:38.000','2016-07-06 01:29:16.000','2016-07-19 22:41:00.000')
INSERT	@Sample VALUES	('2016-07-03 16:04:27.000','2016-07-06 13:01:40.000','2016-07-19 21:21:49.000')
INSERT	@Sample VALUES	('2016-07-03 16:04:27.000','2016-07-06 13:04:02.000','2016-07-19 14:06:40.000')
INSERT	@Sample VALUES	('2016-07-03 16:04:27.000','2016-07-06 13:04:43.000','2016-07-19 20:38:35.000')
INSERT	@Sample VALUES	('2016-07-03 16:05:02.000','2016-07-06 14:40:12.000','2016-07-19 17:15:05.000')

-- Display the initial value
SELECT	receiptdate,createddate, endeddate,
		test2=createddate-endeddate,

		DATEDIFF(hh,createddate , endeddate ) as operational_tat,
		DATEDIFF(hh,receiptdate, endeddate) as overall_tat

FROM	@Sample




Desired Result:
---------------------------------------------------------------------------------------
Receiptdate-----------------Createddate--------------endeddate------------operationaltat--overalltat
2016-07-03 15:59:38.000--2016-07-06 00:29:48.000---2016-07-20 05:02:26.000---16.54---------14.19
2016-07-03 15:59:38.000--2016-07-06 01:29:16.000---2016-07-19 22:41:00.000---16.28---------13.88
2016-07-03 16:04:27.000--2016-07-06 13:01:40.000---2016-07-19 21:21:49.000---16.22---------13.35
2016-07-03 16:04:27.000--2016-07-06 13:04:02.000---2016-07-19 14:06:40.000---15.92---------13.04
2016-07-03 16:04:27.000--2016-07-06 13:04:43.000---2016-07-19 20:38:35.000---16.19---------13.32
2016-07-03 16:05:02.000--2016-07-06 14:40:12.000---2016-07-19 17:15:05.000---16.051--------13.11
2

I got the result but I need to get the max decimal points intead of 2 only.

convert(float,round(DATEDIFF(mi,receiptdate , endeddate )/60.0/24.0,2)) as operational_tat convert(float,round(DATEDIFF(mi,createddate, endeddate)/60.0/24.0,2)) as overall_tat

3

Watch using "FLOAT" it is an "Approximate Numerics" versus "Exact Numerics". In this case it should not be a problem, but I just wanted to point that out.

4

Change the length to whatever you want it to be, for example:

round(DATEDIFF(mi,receiptdate , endeddate )/60.0/24.0,5) as operational_tat
                                                     ^^^
5

Thank you very much.

6

Use the true power hidden in the DATETIME datatype to make this as simple as possible.

 SELECT  receiptdate, createddate, endeddate 
        ,overall_tat     = CAST(endeddate-receiptdate AS DECIMAL(9,2))
        ,operational_tat = CAST(endeddate-createddate AS DECIMAL(9,2))
   FROM @Sample
;