Post reply

Date and time Display

  • 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

  • January 18, 2010 at 3:21 am

    #1104837

    Hi ram,

    In which format of the time you have?

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

  • January 18, 2010 at 3:33 am

    #1104844

    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

  • January 18, 2010 at 3:54 am

    #1104850

    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

  • January 18, 2010 at 4:10 am

    #1104858

    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

  • January 18, 2010 at 5:36 am

    #1104880

    Hi there,

    You looking to divide by 3600

    eg. 18900/3600=5.25

  • January 19, 2010 at 5:37 pm

    #1105781

    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.

  • January 19, 2010 at 9:54 pm

    #1105820

    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)

  • January 19, 2010 at 9:55 pm

    #1105821

    Hi All,

    Thanks for your precious solution.

    Regards,

    Ram

  • January 19, 2010 at 10:15 pm

    #1105829

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

  • January 20, 2010 at 9:37 am

    #1106141

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

  • January 20, 2010 at 10:02 pm

    #1106472

    yip you right:-)

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

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