Date and time Display

Question

Post reply

Date and time Display

RamG

Right there with Babe

Points: 767
More actions
January 18, 2010 at 2:43 am

#156587

Hi
I want to displayed my total time in hours. For example, 7 hours 30 minutes should appear as 7:50 hours (It should be a string format and having only two decimal places). Can anyone help me on this.
Regards,
Ram

Viewing 12 posts - 1 through 11 (of 11 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi ram,

In which format of the time you have?

7.30, 7:30, 7hours 30 min, so that its appears as 7:50

RamG Right there with Babe Points: 767 More actions · Answer 2

Hi,

Thanks for your replay. I have the total seconds.

sum(datediff(second, '01/15/2010 10:35:51', '01/15/2010 11:05:51'))

Using this second i have to display like '0:50'

Regards,

Ram

arun.sas SSChampion Points: 11831 More actions · Answer 3

Ramakrishnan-403797 (1/18/2010)
sum(datediff(second, '01/15/2010 10:35:51', '01/15/2010 11:05:51'))

Hi,

select datediff(second, '01/15/2010 10:35:51', '01/15/2010 11:05:51') /3600.00

select datediff(second, '01/15/2010 10:35:51', '01/15/2010 12:05:51')/3600.00

RamG Right there with Babe Points: 767 More actions · Answer 4

Hi,

Thanks, This query is working fine but i cant use this query. Because my table having only "total_seconds" column and this table contains the values as follow

18900

19800

20700

So i have to convert this seconds to hh:mm format

5.15 which means 18900 seconds should become 5.25
5.30 which means 19800 seconds should become 5.50
5.45 which means 20700 seconds should become 5.75

Means 7 hours 30 minutes should appear as 7:50 hours instead of 7:30 [It should be a string format and having only two decimal places]

Regards,

Ram

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 5

Hi there,

You looking to divide by 3600

eg. 18900/3600=5.25

Donald Burr Ten Centuries Points: 1056 More actions · Answer 6

To get exactly what you want you need to perform a cast twice.

Here is a little test for you.

DECLARE @total_seconds INT

SET @total_seconds = 20700

SELECT CAST( CAST((@total_seconds/3600.00) AS MONEY) AS VARCHAR(50)) AS total_seconds

NOTES:

Be sure to divide by 3600.00 not just 3600. It sets the type correctly so that you get the decimal results.

The 1st cast to money rounds the answer to 100ths and removes the trailing 0's

The 2nd cast gets you the text output you wanted.

Just drop the @ from total_seconds and add the FROM and appropriate WHERE and you have it.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

I've never heard of displaying decimal minutes with a colon instead of a decimal point. At the very least, that will confuse the hell out of the uninitiated. 😉

--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)

RamG Right there with Babe Points: 767 More actions · Answer 8

Hi All,

Thanks for your precious solution.

Regards,

Ram

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 9

Or select cast(18900 as float)/3600 instead of 2 conversions

Donald Burr Ten Centuries Points: 1056 More actions · Answer 10

But that does not give him the Text String he wanted.

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 11

shanu.hoosen

SSCrazy

Points: 2227

January 20, 2010 at 10:02 pm

#1106472

yip you right:-)