# Calculate date and time with decimal

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``````

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```

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.

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
^^^``````

Thank you very much.

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
;``````