Help needed in time conversion

  • Hi,

    declare @val varchar (10) = "20:11";

    How do i display this as 20 hours and 11 minutes. If it is 00:20 then 20 minutes. If it's 20:00 then 20 hours. If its 20 then also 20 hours. Any sample please how to achieve this.

  • KGJ-Dev (11/15/2016)


    Hi,

    declare @val varchar (10) = "20:11";

    How do i display this as 20 hours and 11 minutes. If it is 00:20 then 20 minutes. If it's 20:00 then 20 hours. If its 20 then also 20 hours. Any sample please how to achieve this.

    Quick suggestion

    😎

    USE TEEST;

    GO

    SET NOCOUNT ON;

    ;WITH SAMPLE_DATA(TIMESTR) AS

    (

    SELECT '00:10' UNION ALL

    SELECT '01:01' UNION ALL

    SELECT '02:01' UNION ALL

    SELECT '10:02' UNION ALL

    SELECT '12:00' UNION ALL

    SELECT '24:59'

    )

    SELECT

    SD.TIMESTR

    ,CASE

    WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) = 0 THEN ''

    WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) = 1 THEN '1 hour'

    WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) > 1 THEN LEFT(SD.TIMESTR,2) + ' hours'

    END +

    CASE

    WHEN CONVERT(INT,LEFT(SD.TIMESTR,2),0) > 0 AND CONVERT(INT,RIGHT(SD.TIMESTR,2),0) > 0 THEN ' and '

    ELSE ''

    END +

    CASE

    WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) = 0 THEN ''

    WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) = 1 THEN '1 minute'

    WHEN CONVERT(INT,RIGHT(SD.TIMESTR,2),0) > 1 THEN RIGHT(SD.TIMESTR,2) + ' minutes'

    END AS TIME_OUT

    FROM SAMPLE_DATA SD;

    Output

    TIMESTR TIME_OUT

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

    00:10 10 minutes

    01:01 1 hour and 1 minute

    02:01 02 hours and 1 minute

    10:02 10 hours and 02 minutes

    12:00 12 hours

    24:59 24 hours and 59 minutes

  • thank you Erik.

  • If the input string is limited to less than 24 hours, the following makes life pretty easy and will also produce an error if someone entered improper information that result in more than 24 hours or more than 59 minutes.

    WITH SAMPLE_DATA(TIMESTR) AS

    (

    SELECT '00:10' UNION ALL

    SELECT '01:01' UNION ALL

    SELECT '02:01' UNION ALL

    SELECT '10:02' UNION ALL

    SELECT '12:00' UNION ALL

    SELECT '23:59'

    )

    SELECT DATENAME(hh,TimeStr) + ' Hour(s) and ' + DATENAME(mi, TimeStr) + ' Minute(s)'

    FROM Sample_Data

    ;

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

  • Jeff Moden (11/16/2016)


    If the input string is limited to less than 24 hours, the following makes life pretty easy and will also produce an error if someone entered improper information that result in more than 24 hours or more than 59 minutes.

    WITH SAMPLE_DATA(TIMESTR) AS

    (

    SELECT '00:10' UNION ALL

    SELECT '01:01' UNION ALL

    SELECT '02:01' UNION ALL

    SELECT '10:02' UNION ALL

    SELECT '12:00' UNION ALL

    SELECT '23:59'

    )

    SELECT DATENAME(hh,TimeStr) + ' Hour(s) and ' + DATENAME(mi, TimeStr) + ' Minute(s)'

    FROM Sample_Data

    ;

    Good point Jeff

    😎

  • KGJ-Dev (11/16/2016)


    thank you Erik.

    You are very welcome.

    😎

    You should consider Jeff's fine advice, it is easy to incorporate into the solution.

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

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