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 l...
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