Converting an nvarchar into a custom time format to query upon duration-CodePudding

I am working with a table that has a variety of column types in rather specific and strange formats. Specifically I have a column, 'Total_Time' that measures a duration in the format: days:hours:minutes (d:hh:mm)

e.g 200:10:03 represents 200 days, 10 hours and 3 minutes.

I want to be able to run queries against this duration in order to filter upon time durations such as

SELECT * FROM [TestDB].[dbo].[myData] WHERE Total_Time < 0:1:20

Ideally this would provide me with a list of entries whose total time duration is less than 1 hour and 20 minutes. I'm not aware of how this is possible in an nvarchar format so I would appreciate any advice on how to approach this problem. Thanks in advance...

CodePudding user response：

I would suggest converting that value to minutes, and then passing the parametrised value as minutes as well.

If we can assume that there will always be a days, hours, and minutes section (so N'0:0:10' would be used to represent 10 minutes) you could do something like this:

SELECT *
FROM (VALUES(N'200:10:03'))V(Duration)
     CROSS APPLY (VALUES(CHARINDEX(':',V.Duration)))H(CI)
     CROSS APPLY (VALUES(CHARINDEX(':',V.Duration,H.CI 1)))M(CI)
     CROSS APPLY (VALUES(TRY_CONVERT(int,LEFT(V.Duration,H.CI-1)),TRY_CONVERT(int,SUBSTRING(V.Duration,H.CI 1, M.CI - H.CI-1)),TRY_CONVERT(int, STUFF(V.Duration,1,M.CI,''))))DHM(Days,Hours,Minutes)
     CROSS APPLY (VALUES((DHM.Days*60*24)   (DHM.Hours * 60)   DHM.Minutes))D(Minutes)
WHERE D.[Minutes] < 80; --1 hour 20 minutes = 80 minutes

If you can, then ideally you should be fixing your design and just storing the value as a consumable value (like an int representing the number of minutes), or at least adding a computed column (likely PERSISTED and indexed appropriately) so that you can just reference that.

If you're on SQL Server 2022 , you could do something like this, which is less "awful" to look at:

SELECT *
FROM (VALUES(N'200:10:03'))V(Duration)
     CROSS APPLY(SELECT SUM(CASE SS.ordinal WHEN 1 THEN TRY_CONVERT(int,SS.[value]) * 60 * 24
                                            WHEN 2 THEN TRY_CONVERT(int,SS.[value]) * 60
                                            WHEN 3 THEN TRY_CONVERT(int,SS.[value])
                            END) AS Minutes
                 FROM STRING_SPLIT(V.Duration,':',1) SS)D
WHERE D.[Minutes] < 80; --1 hour 20 minutes = 80 minutes;