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

Mohammed Muntazim 0
Viewed 41
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
sql-server sql
2 Answers

ISDATE unfortunately has issues because it does not necessarily look for a format that would convert via CONVERT or CAST. You may want to just use TRY_CAST.

CREATE OR ALTER FUNCTION dbo.DateValidation (
    @P_Input NVARCHAR(55)
)
RETURNS NVARCHAR(55)
AS
BEGIN
    RETURN ISNULL(CAST(TRY_CAST(@P_Input AS DATE) AS nvarchar(55)), N'NOT IN DATE FORMAT');
END;

I have no idea why you would want to do this, as you still end up with a nvarchar, and scalar functions can be slow. You are probably best off just using the following directly in whatever query you have

SELECT TRY_CONVERT(date, YourValue, 102)   -- 102 is the style number, pick the correct one
FROM ...
Charlieface0

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
Meyssam Toluie0
Related