Forum Discussion

RajSQLServer2019's avatar
RajSQLServer2019
Copper Contributor
Aug 26, 2021

string column values - convert into hours

Hi All,

Thanks a lot for your time; i have seen many examples where we convert the datetime into days:hours:mins - but i have the other way around - i have a string or text column which has values like
   Ticket_Number                  Total Duration

    JIRA123                             2 Days 10 Hours 15 Minutes

    JIRA456                             0 days 1 hour 12 minutes

    JIRA999                             45 minutes

    JIRA888                           13 minutes

    JIRA777                           null

    JIRA666                         15 days 17 hours 45 minutes

What do I Need? convert into hours - show in hours like 18.5 or 19.56 hours 

i have the SQL server table with this layout.. without going for stored procedures/functions is this something that can be done using the select clause..

again, thanks a lot

1 Reply

  • VladimirMold's avatar
    VladimirMold
    Copper Contributor
    Aug 29, 2021

    RajSQLServer2019 

     

    How about something like this?

    DECLARE @tmp TABLE
(
     Ticket_Number nvarchar(100)
     ,TotalDuration nvarchar(100)
)
INSERT INTO @tmp VALUES
    ('JIRA123','2 Days 10 Hours 15 Minutes')
    ,('JIRA456','0 days 1 hour 12 minutes')
    ,('JIRA999','45 minutes')
    ,('JIRA888','13 minutes')
    ,('JIRA777','null')
    ,('JIRA666','15 days 17 hours 45 minutes')
    ,('JIR???','17 hours 45 minutes') -- added 

SELECT
    t.*
    ,clean.TotalDuration
    ,cleaned.TotalDuration
    ,Time_Hours = NULLIF(CAST(
        ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 3) as decimal(9,2)) * 24, 0) -- days
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 2) as decimal(9,2)), 0) -- hours
        + ISNULL(TRY_CAST(PARSENAME(cleaned.TotalDuration, 1) as decimal(9,2)) / 60.0, 0) -- minutes
        as decimal(9,2)), 0)
    --,Hours = CASE WHEN CHARINDEX('Days', t.TotalDuration) > 0 THEN LEFT(t.TotalDuration, CHARINDEX('Days', t.TotalDuration) -1) END -- TRY_CAST(  as int) * 24
FROM @tmp t
CROSS APPLY(SELECT TotalDuration = CASE WHEN t.TotalDuration != 'null' THEN REPLACE(REPLACE(t.TotalDuration, ' hour ', ' hours '), 'null', '') END) clean
CROSS APPLY(SELECT TotalDuration = '"' +REPLACE(REPLACE(REPLACE(clean.TotalDuration, 'hours', '"."'), 'days', '"."'), 'minutes', '"')) cleaned

     

Resources