SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rdsb_2170
rdsb_2170
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 101
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
rdsb_2170
rdsb_2170
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 101
Still struggling with this issue. If no one has a solution, any suggestions on what to search for on internet?
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82557 Visits: 21132
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 :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
rdsb_2170
rdsb_2170
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 101
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
rdsb_2170
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 101
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
Luis Cazares
SSC Guru
SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)SSC Guru (162K reputation)

Group: General Forum Members
Points: 162583 Visits: 22759
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
rdsb_2170
Old Hand
Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)Old Hand (376 reputation)

Group: General Forum Members
Points: 376 Visits: 101
Hi Luis,

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

Best Regards,
Cervello
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search