0

I'm retrieving data from ServiceNow using the REST API and for some of the columns I get a value similar to "2 Days 4 Hours 43 Minutes 29 Seconds".

How can I convert this into something useful?

The STRING_SPLIT function doesn't seem to help as it will split the values but then I can't combine them back together.

Or is there some way to have it converted in the API before it gets to the SQL database?

Dale K
  • 25,246
  • 15
  • 42
  • 71
PhoenixC46
  • 11
  • 3
  • 1
    Let's start with the obvious - "2 days ..." is not a datetime value. But you need to pull apart the string and convert each one into a common unit - probably seconds. Add all the seconds values together and the use dateadd with the appropriate base date. – SMor Jun 29 '20 at 20:52
  • Any suggestions how I can actually do that? The values i'm getting are anything from "17 Seconds" to days and hours, so it's not easy to split up as there isn't always a leading space or doesn't always have the seconds. – PhoenixC46 Jun 29 '20 at 21:06
  • Asides: From [`String_Split`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15): "The output rows might be in any order. The order is _not_ guaranteed to match the order of the substrings in the input string." A [`time`](https://learn.microsoft.com/en-us/sql/t-sql/data-types/time-transact-sql?view=sql-server-ver15) would be convenient, but it is limited to values < 24 hours. – HABO Jun 29 '20 at 22:06

1 Answers1

0

ok, think I've figured out how to split the values using a combination of LEFT, SUBSTRING and CHARINDEX.

The result is something like this:

,CASE WHEN [calendar_duration] LIKE '%Day%' THEN LEFT(SUBSTRING([calendar_duration],CHARINDEX(' Day', [calendar_duration]) -2, 2),CHARINDEX(' Day', [calendar_duration]) - 1) ELSE '00' END AS [Days]
  ,CASE WHEN [calendar_duration] LIKE '%Hour%' THEN LEFT(SUBSTRING([calendar_duration],CHARINDEX(' Hour', [calendar_duration]) -2, 2),CHARINDEX(' Hour', [calendar_duration]) - 1) ELSE '00' END AS [Hours]
  ,CASE WHEN [calendar_duration] LIKE '%Minute%' THEN LEFT(SUBSTRING([calendar_duration],CHARINDEX(' Minute', [calendar_duration]) -2, 2),CHARINDEX(' Minute', [calendar_duration]) - 1) ELSE '00' END AS [Minutes]
  ,CASE WHEN [calendar_duration] LIKE '%Second' THEN LEFT(SUBSTRING([calendar_duration],CHARINDEX(' ', [calendar_duration]) -2, 2),CHARINDEX(' Seconds', [calendar_duration]) - 1) ELSE '00' END AS [Seconds]

Converting each value into seconds then summing everything up should be much easier :-)

I'm sure there are more efficient ways to do this but it won't be in frequent use so it'll do for now.

PhoenixC46
  • 11
  • 3