not able to use my user defined function

Question

Post reply

not able to use my user defined function

DaveBriCam

SSCarpal Tunnel

Points: 4298

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: 4298 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: 246960 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, 11 months ago by Phil Parkin. Reason: Fix typo

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ratbak Ten Centuries Points: 1358 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: 4298 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246960 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 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: 4298 More actions · Answer 7

DaveBriCam

SSCarpal Tunnel

Points: 4298

December 4, 2020 at 4:46 pm

#3816999

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