not able to use my user defined function

  • 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
  • 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.
  • 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 2 months, 3 weeks ago by  Phil Parkin. Reason: Fix typo

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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?

     

     

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

  • 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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
  • Solved... there were no NULLS but one value was ''

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

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