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.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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 16
Argument data type text is invalid for argument 1 of reverse function.

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.