Home Conversion failed when converting the varchar value 'SELECT ISDATE(@P_Input)' to data type int

Questions

Conversion failed when converting the varchar value 'SELECT ISDATE(@P_Input)' to data type int

September 19, 2022

ALTER FUNCTION[dbo].[DateValidation]
(
@P_Input NVARCHAR(55)
)
RETURNS NVARCHAR(55)
AS
BEGIN
    DECLARE @V_Results DATE
    DECLARE @V_Result INT
    DECLARE @V_ERROR NVARCHAR(MAX)
    set @V_Result='SELECT ISDATE(@P_Input)'
    IF @V_Result=1
    BEGIN
     SET @V_Results='SELECT CONVERT(P_Input,getDate(),2)'
      RETURN @V_Results
    END
    SET @V_ERROR ='NOT IN DATE FORMAT'
    RETURN @V_ERROR
END
GO

>Solution :

First of all you are setting variable @V_Result with string (Command). If you need to make query dynamic then you should execute it:

EXEC ('SELECT ISDATE(@P_Input)')

But in your case, no need to create dynamic command, See the improved format of your function:

CREATE OR ALTER FUNCTION dbo.DateValidation (
    @P_Input NVARCHAR(55)
)
RETURNS NVARCHAR(55)
AS
BEGIN
    DECLARE @V_Result NVARCHAR(55);

    IF (ISDATE(@P_Input) = 1)   
        SET @V_Result = CAST(@P_Input AS DATE)
    ELSE
        SET @V_Result = N'NOT IN DATE FORMAT';

    RETURN @V_Result;
END;
GO