Extract Time from non standard time field

Question

Post reply

Extract Time from non standard time field

qamar 52306

SSC Enthusiast

Points: 168
More actions
September 7, 2012 at 3:55 am

#263941

I have a feild in a table of type varchar that contains time as
hhh:mm:ss, ss and mm is upto 59 but hhh is unrestricted
e.g 73:59:59 or 144:44:59
What I want is to get time in hours, can anyone help me write a query???

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

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

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

SELECT

TimeVC,

FirstColonPosition = CHARINDEX(':',TimeVC,1),

[Hours] = LEFT(TimeVC,CHARINDEX(':',TimeVC,1)-1)

FROM (

SELECT TimeVC = '73:59:59' UNION ALL

SELECT '144:44:59') d

^{“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

qamar 52306 SSC Enthusiast Points: 168 More actions · Answer 2

Thanks cris, you helped me solve my issue

and I will definately follow forum's rules next time 😉

Thanks alot 🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Borrowing on Chris' code, here's one way to convert the time to decimal hours.

SELECT TimeVC,

[Hours] = LEFT(TimeVC,CHARINDEX(':',TimeVC,1)-1)

+ CONVERT(FLOAT,CONVERT(DATETIME,'00'+SUBSTRING(TimeVC,CHARINDEX(':',TimeVC,1),8000)))*24

FROM (

SELECT TimeVC = '73:59:59' UNION ALL

SELECT '144:44:59'

) d

Results:

TimeVC Hours

--------- ----------------------

73:59:59 73.9997222222222

144:44:59 144.749722222222

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)