Hi snehalp,
Refer below query.
SQL
DECLARE @Test AS TABLE(ID INT,Name VARCHAR(50),CALLDATE DATETIME)
INSERT INTO @Test VALUES(1,'XYZ','2019-05-03 09:00:00')
INSERT INTO @Test VALUES(2,'PQR','2019-05-02 14:00:00')
INSERT INTO @Test VALUES(3,'ABC','2019-05-01 09:00:00')
INSERT INTO @Test VALUES(4,'UVW','2019-05-03 03:00:00')
DECLARE @CurrentTime DATETIME
SET @CurrentTime = '2019-05-03 15:00:00'
SELECT ID,Name,
CASE WHEN DATEDIFF(HOUR, CALLDATE, @CurrentTime) <= 24 THEN CAST(DATEDIFF(HOUR, CALLDATE, @CurrentTime) AS VARCHAR) + ' Hours'
ELSE CAST(DATEDIFF(DAY, CALLDATE, @CurrentTime) AS VARCHAR) + ' Days ' + CAST((DATEDIFF(HOUR, CALLDATE, @CurrentTime) % 24) AS VARCHAR) + ' Hours'
END AS 'Time'
FROM @Test
Output
| ID |
Name |
Time |
| 1 |
XYZ |
6 Hours |
| 2 |
PQR |
1 Days 1 Hours |
| 3 |
ABC |
2 Days 6 Hours |
| 4 |
UVW |
12 Hours |
If you want to more accurate value you need to create your own function and implement it in the select query.
