not able to use my user defined function

Question

Post reply

not able to use my user defined function

DaveBriCam

SSCarpal Tunnel

Points: 4294

December 4, 2020 at 2:21 pm

#3816957

I have a function I have created and need some help in how to use it in a query:

CREATE FUNCTION [dbo].[fnGetSeconds]
(
@TimeFormatted varchar(max)
)
RETURNS decimal(10, 2)
AS
BEGIN
  RETURN
  (SELECT (LEFT(@TimeFormatted,2)*3600) +
       ROUND(DATEDIFF(MS, 0, '00' + RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2))/1000.0,0)
        AS 'TimeSeconds')
END

--Need to use it in the query below:

select [call_id], [call_time], 

--convert call time to decimal using function
             AS [call time decimal],

         from dbo.CallLog

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

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

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 1

I can figure out where/how to use it but I'm getting an error:

dbo.fnGetSeconds(CALL_TIME)

--Invalid length parameter passed to the RIGHT function.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

DaveBriCam wrote:

I can figure out where/how to use it but I'm getting an error:
dbo.fnGetSeconds(CALL_TIME)

--Invalid length parameter passed to the RIGHT function.

There's something about one or more items of data which are being passed to the function which is causing that error. Do you have any NULLs, perhaps?

This reply was modified 3 years, 4 months ago by Phil Parkin. Reason: Fix typo

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ratbak Ten Centuries Points: 1328 More actions · Answer 3

Sample data that is failing? It sounds like perhaps you have a @TimeFormatted value that is only 1 character.

DECLARE @TimeFormatted varchar(max) = '2'
SELECT RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2);

Do you really have inputs that are over 8000 characters? You might want to consider a more realistic length for your @TimeFormatted parameter. And given the error on the RIGHT() function, you may need to fix or filter the data to exclude invalid values. Perhaps even better, can you store and use real datetimes (even if in a computed column) so that you don't have to worry about string formatting?

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 4

No nulls in the data but there are some that are 00:00:00

Phil Parkin SSC Guru Points: 246686 More actions · Answer 5

DaveBriCam wrote:

No nulls in the data but there are some that are 00:00:00

I currently have nothing to test this with. What happens if you pass that value to your function?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 6

I changed the function to have a length of 10 rather than MAX but the error persists:

CREATE FUNCTION [dbo].[fnGetSeconds]
(
@TimeFormatted varchar(10)
)
RETURNS decimal(10, 2)
AS
BEGIN
  RETURN
  (SELECT (LEFT(@TimeFormatted,2)*3600) +
       ROUND(DATEDIFF(MS, 0, '00' + RIGHT(@TimeFormatted,LEN(@TimeFormatted)-2))/1000.0,0)
        AS 'TimeSeconds')
END

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 7

DaveBriCam

SSCarpal Tunnel

Points: 4294

December 4, 2020 at 4:46 pm

#3816999

Solved... there were no NULLS but one value was ''