Converting Time to Decimal

Question

Post reply

Converting Time to Decimal

DiabloSlayer

SSCrazy

Points: 2232
More actions
February 27, 2014 at 10:43 am

#284166

Greetings everyone,
First of all I hope the Post Title is accurate as to what I'm asking your help for.
Here is the challenge I'm running into, I have the following table that gives me PERSON, DATE and MINUTE . The minutes are stored just as a whole number in the database i,e '10', I would like to do convert the value from 10 to 00:17 because 10 is in hours and minutes and I need it in decimal, so in decimal I would get 0.16666 which is rounded to 0.17.
So maybe first convert 10 to 00:10 then to decimal as 00:17.
Thank you,
WITH SampleData (PERSON, [DATE], [MINUTE]) AS
(
SELECT 1125,'27/01/2014',10
UNION ALL SELECT 1125,'27/01/2014',3
UNION ALL SELECT 1125,'27/01/2014',1
)
SELECT *
FROM SampleData;

Current Result
PERSONDATE MINUTE
112527/01/201410
112527/01/20143
112527/01/20141
Desired Result
PERSONDATE MINUTE
112527/01/201400:17
112527/01/201400:05
112527/01/201400:02

Viewing 6 posts - 1 through 5 (of 5 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm giving you 2 solutions depending on the format you want.

WITH SampleData (PERSON, [DATE], [MINUTE]) AS

(

SELECT 1125,'27/01/2014',70

UNION ALL SELECT 1125,'27/01/2014',3

UNION ALL SELECT 1125,'27/01/2014',1

)

SELECT *,

CAST( (MINUTE / 60.) AS decimal(5,2)),

REPLACE( CAST( (MINUTE / 60.) AS decimal(5,2)) , '.', ':')

FROM SampleData;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 2

Hi Luis,

This is AWESOME..

Thank you very much.

I will give this a go when I next log into my clients system.

Thank you again.

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 3

Hi Luis,

Just wanted to let you know that your solution worked like a charm so thank you thank you.

Best Regards,

Zulf

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You're welcome.

It might look simple, but I want to be sure that you understand how does it work. Do you have any questions?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 5

Hi Luis,

I used the CAST solution, I will study this in more detail so at the moment I don't have any questions.

Best Regards,

ZA