#Error when converting HH:MM:SS to seconds using expression

Question

Post reply

#Error when converting HH:MM:SS to seconds using expression

rdsb_2170

SSC Eights!

Points: 817
More actions
February 9, 2018 at 11:55 am

#403106

Greetings!!
I have the following expression that is returning a #Error, and I'm stumped on what to try next. Any help would be greatly appreciated.
SLAHours is either 00:00:00 or 000:00:00 (if hours >99).
Expression:
=IIF(Len(Fields!SLAHours.Value) = 9,
(left(Fields!SLAHours.Value,3)*3600) +(mid(Fields!SLAHours.Value,5,2)*60) + (right(Fields!SLAHours.Value,2)),
IIF(Len(Fields!SLAHours.Value) = 8,
(left(Fields!SLAHours.Value,2)*3600) +(mid(Fields!SLAHours.Value,4,2)*60) + (right(Fields!SLAHours.Value,2)),0))
Note: I even tried
=IIF(Len(Fields!SLAHours.Value) = 9,
(left(Fields!SLAHours.Value,3)*3600) +(mid(Fields!SLAHours.Value,5,2)*60) + (right(Fields!SLAHours.Value,2)),
(left(Fields!SLAHours.Value,2)*3600) +(mid(Fields!SLAHours.Value,4,2)*60) + (right(Fields!SLAHours.Value,2)))
If I just set it to (left(Fields!SLAHours.Value,2)*3600) +(mid(Fields!SLAHours.Value,4,2)*60) + (right(Fields!SLAHours.Value,2)), then the rows where there are 3 place holders for the hours returns an error.
Thank you,
Cervello

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rdsb_2170 SSC Eights! Points: 817 More actions · Answer 1

Still struggling with this issue. If no one has a solution, any suggestions on what to search for on internet?

Thom A SSC Guru Points: 99130 More actions · Answer 2

You don't explain what your goal is here, nor do we have any data to work with. All we know is that your code generates an error; this doesn't tell us what's wrong, what it's trying to do, or anything.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

rdsb_2170 SSC Eights! Points: 817 More actions · Answer 3

My apologies.
I’m using a scalar function to return SLAHours, in my stored procedure (below). I have identified a need to convert those hours back to seconds in my report, so that I can insert an indicator (of exceeds SLA threshold) using seconds in my range. The formula that I’m getting the error for is the convert SLAHours back to seconds.

The SLA hours is set to accept hours exceeding 99, in case the agent is not closing requests quickly. Example: Some rows my only have HH:MM:SS while other rows may show HHH:MM:SS.
I was trying to use the length of the data within the field LEN() to set a condition by which to apply the formula. This is my formula, which is not working properly.

SELECT req.[ID]
       ,req.[RequestTypeId]
       ,reqt.[RequestType]
       ,req.[RequestStatusId]
,reqs.[RequestStatus]
       ,req.[DateSubmitted] as RequestDate
       ,DATENAME(dw,req.DateSubmitted) as RequestDateName
       ,req.[Department]
       ,req.[LeadId]
       ,req.[PropertyCode]
       ,Prpty.Property
       ,Prpty.Location
       ,req.[TourSite]
       ,Office.[Location] as TourLocation
       ,req.[DateClosed] as FinalStatusDate
       ,DATENAME(dw,req.DateClosed) as FinalStatusDateName
,[I3_CMS].[dbo].[fnFullfillmentSLAHoursCalc](req.DateSubmitted, ISNULL(req.[DateClosed],'1/1/1900')) as SLAHours
       ,req.[ClosedBy] as ClosingAgentMSLogin
       ,users.TSR as ClosingAgentTSR
FROM [HUB].[RMS].[Requests] req
left join [HUB].[RMS].[RequestType] reqt ON req.[RequestTypeId] = reqt.[RequestTypeId]
left join [HUB].[RMS].[RequestStatus] reqs ON req.[RequestStatusId] =     reqs.[RequestStatusId]
left Join [HUB].[dbo].[aspnet_Users] users ON req.[ClosedBy]= users.[UserName]
left Join [HUB].[RMS].[PropertyLocation] Prpty ON req.[PropertyCode] = Prpty.[PropertyCode]
left join [HUB].[RMS].[Offices] Office ON req.TourSite = Office.[Office]
Where [DateSubmitted] >= @StartDateTime and [DateSubmitted] <= @EndDateTime AND
REQ.[RequestTypeId] in ('1','2','3')
Order by LeadID

rdsb_2170 SSC Eights! Points: 817 More actions · Answer 4

Correction. This is my formula that is resulting in the error.

Expression:
=IIF(Len(Fields!SLAHours.Value) = 9,
(left(Fields!SLAHours.Value,3)*3600) +(mid(Fields!SLAHours.Value,5,2)*60) + (right(Fields!SLAHours.Value,2)),

IIF(Len(Fields!SLAHours.Value) = 8,
(left(Fields!SLAHours.Value,2)*3600) +(mid(Fields!SLAHours.Value,4,2)*60) + (right(Fields!SLAHours.Value,2)),0))

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Have you tried something like this?
(left(Fields!SLAHours.Value,Len(Fields!SLAHours.Value)-6)*3600) +(mid(Fields!SLAHours.Value,Len(Fields!SLAHours.Value)-4,2)*60) +(right(Fields!SLAHours.Value,2))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rdsb_2170 SSC Eights! Points: 817 More actions · Answer 6

Hi Luis,

That worked perfectly!! Thank you very much for helping me with this.

Best Regards,
Cervello