Time Conversion Issue

Question

Post reply

Time Conversion Issue

abhas

SSCarpal Tunnel

Points: 4681
More actions
February 11, 2014 at 11:58 pm

#304313

Hi All,
I want to convert time into hours as per below format. Please guide.
For example – 2:15:00 would be computed as 2.25 HRS or 6:30:00 would show as 6.5 HRS.
Kindly help how can i do this.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

milos.radivojevic Default port Points: 1499 More actions · Answer 1

DECLARE @t AS TIME = '2:15:00.000';

SELECT

DATEPART(hour, @t) +

(

DATEPART(minute, @t) * 60 +

DATEPART(second, @t) + DATEPART(millisecond, @t) * 1.0/1000

) * 1.0/3600 AS FormattedTime

___________________________
Do Not Optimize for Exceptions!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

abhas (2/11/2014)
Hi All,
I want to convert time into hours as per below format. Please guide.
For example – 2:15:00 would be computed as 2.25 HRS or 6:30:00 would show as 6.5 HRS.
Kindly help how can i do this.

Solutions will depend upon the datatype of your 'time' column. Can you post a CREATE TABLE - it doesn't have to contain anything more than your 'time' column - and a few INSERTS please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 3

Thanks Milos. 🙂

🙂 🙂 🙂

Its working. Thanks a lot