Passing variable precision to TIMEFROMPARTS function-CodePudding

I'm trying to build a function that takes a string as input representing a time variable with no separators. The fraction part could have variable precision.

This is the code I came up with :

CREATE FUNCTION [dbo].[Convert_FullStringToTime]
(
    -- Add the parameters for the function here
    @inputString VARCHAR(17),
    @fractionsPrecision INT = 0
)
RETURNS TIME(7)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result TIME(7)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = 
        TIMEFROMPARTS(
            LEFT(@inputString, 2),                      --hh
            SUBSTRING(@inputString, 3, 2),              --mm
            SUBSTRING(@inputString, 5, 2),              --ss
            RIGHT(@inputString, @fractionsPrecision),   --ff
            @fractionsPrecision
        )

    -- Return the result of the function
    RETURN @Result
END

But I'm getting an error:

Scale argument is not valid. Valid expressions for data type time scale argument are integer constants and integer constant expressions.

Do I have to understand I really have to write a constant for the precision parameter ???
Why on earth has this been done that way ?

Is there a better solution than using a case statement to overcome this ridiculous problem ?

CodePudding user response：

The error is actually telling you that what you want to do is explicitly not allowed. The precision parameter for TIMEFORPARTS must be a literal; it cannot be an expression, a column value or a variable. For your FUNCTION there is, in truth, little/no point in having a "variable" precision value either; a FUNCTION must also have an explicit data type defined and that include the length/scale/precision. You initially had your FUNCTION defined as a time, which is a synonym for time(7), and you have now change the FUNCTION to demonstrate that. As such it wouldn't matter what value the precision was passed, your FUNCTION would always return a time(7).

To address your latter comments:

Why on earth has this been done that way ?

Is there a better solution than using a case statement to overcome this ridiculous problem ?

I'm going to address your second comment first; it isn't a "ridiculous problem", the real problem is you want a value that doesn't have a strict definition. T-SQL is a declarative and compiled langauge; you need to explicity define what data types a value is. The fact you want a variable precision value is a strong indication of an XY Problem, however, what that problem is I don't know. There likely is a "better solution" to what you actually want to do, but without knowing what that is, we can't access that question here.

As for why it's done that way, I come back to the point of that the language is declarative and compiled; as such if you could pass a variable/expression then SQL Sevrer would know what data type to compile a column as. Take the following very simple statement:

SELECT TIMEFROMPARTS(0,0,0,0,V.I) AS T
FROM (VALUES(5),(6),(7))V(I)

Here the column V.I being (trying to be) used to define the precision for TIMEFROMPARTS. The problem though is that V.I has 3 different values, 5, 6, and 7, so what is the data type for the column defined as T? Should it be 7? 5? SQL Server doesn't know, because at the time it compiles the statement it cannot make an informed decision; for a table (rather than a VALUES table construct) the compiler wouldn't read the table first to then make a decision, that would be awful for performance.

As such you must use a literal to tell SQL Server what precision you need. Just like when you define a variable, you can't use the syntax varchar(@Len) or when you CONVERT a column you can't use the syntax CONVERT(decimal(T.P,T.S),MyColumn).

CodePudding user response：

This the combination of function I came up with, taking into account the precision inherent to the TIME datatype. (Thanks to @Larnu)

First, the padding function (which SQL SERVER also lacks...)

CREATE FUNCTION [dbo].[Rpad] 
(
    -- Add the parameters for the function here
    @inputString VARCHAR(MAX),
    @paddingCharacter VARCHAR(1),
    @paddingLength INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result VARCHAR(MAX)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = 
        LEFT(@inputString   REPLICATE(@paddingCharacter, @paddingLength), @paddingLength)

    -- Return the result of the function
    RETURN @Result

END
GO

Then the Time conversion function, which will always return a 7 precision TIME data type.

CREATE FUNCTION [dbo].[Convert_FullStringToTime]
(
    -- Add the parameters for the function here
    @inputString VARCHAR(13)
)
RETURNS TIME(7)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result TIME(7)

    -- Add the T-SQL statements to compute the return value here
    SELECT @Result = 
        TIMEFROMPARTS(
                LEFT(@inputString, 2),                        --hh
                SUBSTRING(@inputString, 3, 2),                --mm
                SUBSTRING(@inputString, 5, 2),                --ss
                RIGHT([dbo].RPAD(@inputString, '0', 7), 7),   --ff
                7
            )


    -- Return the result of the function
    RETURN @Result
END