String Parser to the rescue
Today I encountered a table that someone had decided to put in a semi colon delimited string column with hours that were stored as the number of seconds in the day. So it contained values that looked like:
27000;41400;59400;79200; Which were supposed to represent 7:30, 11:30, 16:30 and 22:00.
I had two problems 1 get the comma delimited fields into columns and 2 get the times into hours.
For step one I used the function from this thread. Its a bit old but I'm still on SQL Server 2008 R2 so it was perfect.
When I tried it right out of the box I recieved the following error:
Msg 8116, Level 16, State 1, Procedure fn_UDF_ParseString, Line 16Argument data type text is invalid for argument 1 of reverse function.
So I changed the datatype in the text parameter from TEXT to NVARCHAR(max) and it worked just fine.
You then call it like this :
dbo.fn_UDF_ParseString(-1,';',ht.OccurrenceTimes) as Occurence_1, -1 for the first delimiter and -2 for the second and so on.
I was close now because My query results look like:
Great now I needed to get the time from seconds of the day into hours and minutes so the end users would have a pleasant view of the data.
I referenced http://blog.sqlauthority.com/2014/08/17/sql-server-convert-seconds-to-hour-minute-seconds-format/ as I'm a huge fan of Pinal Dave.
I only needed hours and minutes so I just used 2/3 of the conversion
ISNULL(RIGHT('0' + CAST(ots.Occurence1 / 3600 AS VARCHAR),2),'') + ':' +
ISNULL(RIGHT('0' + CAST((ots.Occurence1 / 60) % 60 AS VARCHAR),2),'') AS occurence_1,
I then added the isnull as I like to format all my concatenations with that so my output is consistent with a value. I most often use crystal and am ok with the blank (:) placeholder.
I also elected to take the parse function and insert that result into a temp table and then do the string conversion in the query from the temp table for readibility from a future standpoint but it worked perfectly.