Conversion failed when converting datetime from character string

Question

I have the following table and variable

DECLARE @FunctionValue nvarchar(255)
DECLARE @TSV_WithTarget TABLE
        (
            Transition_Set_Variable_ID INT,
            [Value] nvarchar(255),
            TargetTable nvarchar(255),
            TargetColumn nvarchar(255),
            FunctionValue nvarchar(255)
        )

The table contains the following data :

IF (SELECT FunctionValue 
    FROM @TSV_WithTarget 
    WHERE Transition_Set_Variable_ID = @TSV_Target_Counter
    AND FunctionValue IS NOT NULL ) IS NOT NULL
BEGIN
    SELECT FunctionValue 
    FROM @TSV_WithTarget 
END

enter image description here

apparently, this code right here produces the following error if I uncomment any of the commented out lines.

SET @FunctionValue = ( SELECT CASE
                    --WHEN FunctionValue LIKE 'DATEADD%' THEN ( SELECT FunctionValue 
                    --                                          FROM @TSV_WithTarget 
                    --                                          WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
                    WHEN FunctionValue LIKE 'GETDATE' THEN ( SELECT GETDATE() )
                    --WHEN FunctionValue LIKE 'STORED PROCEDURE' THEN ( SELECT Changed_In_SP
                    --                                                  FROM BPE_T_VA_Transition_Set_Variable
                    --                                                  WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
                    --WHEN FunctionValue LIKE 'SWITCH_USER' THEN ( SELECT 'hersem' )
                    WHEN FunctionValue LIKE 'VALUE' THEN ( SELECT Name 
                                                            FROM BPE_T_VA_Value
                                                            WHERE Value_ID = ( SELECT Set_To_Value_ID
                                                                                FROM BPE_T_VA_Transition_Set_Variable
                                                                                WHERE Transition_Set_Variable_ID = @TSV_Target_Counter ) )
                    WHEN FunctionValue LIKE 'VARIABLE' THEN ( SELECT Value_ID
                                                                FROM BPE_T_VA_Process_Instance_Value
                                                                WHERE Variable_ID = ( SELECT Set_To_Variable_ID
                                                                                        FROM BPE_T_VA_Transition_Set_Variable
                                                                                        WHERE Transition_Set_Variable_ID = @TSV_Target_Counter )
                                                                AND Process_Instance_ID = @Process_Instance_ID)
                    --ELSE ( SELECT FunctionValue )
                END AS FV
                FROM @TSV_WithTarget
                WHERE Transition_Set_Variable_ID = @TSV_Target_Counter
                AND FunctionValue IS NOT NULL )

Msg 241, Level 16, State 1, Line 83 Conversion failed when converting datetime from character string.

I have no idea what this could be.

The setting of @FunctionValue takes place in a WHILE loop.

Oleksandr Fedorenko · Accepted Answer · 2013-04-29 11:12:50Z

1

The values of the CASE expression must be of the same type. Hence it is necessary to replace

SELECT GETDATE() on CONVERT(nvarchar(10), GETDATE(), 101) and Value_ID on CAST(Value_ID AS nvarchar(255))

edited Apr 29, 2013 at 11:12

answered Apr 29, 2013 at 11:03

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hermann Over a year ago

Conversion failed when converting the nvarchar value '04/29/2013' to data type int.

Oleksandr Fedorenko Over a year ago

and replace Value_ID on CAST(Value_ID AS nvarchar(255))

hermann Over a year ago

now it works, please add this to your original answer. thank you.

valex · Accepted Answer · 2013-04-29 11:14:49Z

1

The issue here is that the column FV in the result table can get different types depends on the rows order in the table @TSV_WithTarget. You should convert ALL CASE results to ONE type nvarchar.

For example - the first not NULL row is GETDATE so SQL defines FV as a datetime. And so on.

answered Apr 29, 2013 at 11:14

valex

24.2k7 gold badges47 silver badges62 bronze badges

Collectives™ on Stack Overflow

Conversion failed when converting datetime from character string

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related