How to get decimal hour value in datediff function?

Question

Here is the sample:

select datediff(hour,'2023-12-28 09:00:00', '2023-12-28 18:30:00') 

The output value is '9'. However, I need '9.5' which is 9 hours and 30 minutes.

Thanks in advance.

Answer 1

Hi @Lora

Refering from this doc:

Return Value The int difference between the startdate and enddate, expressed in the boundary set by datepart.

You could modify the datepart from hour to second and then divide by 3600.

Like this: select cast(datediff(s,'2023-12-28 09:00:00', '2023-12-28 18:30:00') as float) / 3600

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

However**, I need '9.5'**

Then get the diff in minutes and divide by 60.

select datediff(MINUTE,'2023-12-28 09:00:00', '2023-12-28 18:30:00') / 60.0

Answer 3

Thanks for posting your question in the Microsoft Q&A forum.
I would get diff in minutes and cast the division to decimal as follows:

SELECT    CAST(datediff(minute, '2023-12-28 09:00:00', '2023-12-28 18:30:00') AS decimal(5, 2)) / 60 AS hours_difference; 

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **