CODEWITHSUNDEEP
common-table-expression
user2625113
user2625113

Reputation: 21

Types don't match between the anchor and the recursive part in column of recursive query

CREATE FUNCTION [dbo].FN_GET_ALL_DATES_WITH_DAY_NAME    
(         
    @DateFrom varchar(25)    
    ,@DateTo varchar(25)    
    ,@DayName varchar(25)    
)

RETURNS

@ParsedList table    
(    
    ListValue varchar(25)
)

AS

BEGIN
;WITH ALLDATES ( date )
AS
(
    SELECT @DateFrom 
    UNION ALL
    SELECT DATEADD(d,1,date)
    FROM ALLDATES
    WHERE date < @DateTo
)

SELECT date FROM ALLDATES WHERE DATENAME(dw, date) = @DayName

RETURN 

END

I am having this error:

Msg 240, Level 16, State 1, Procedure FN_GET_ALL_DATES_WITH_DAY_NAME, Line 18 Types don't match between the anchor and the recursive part in column "date" of recursive query "ALLDATES".

Any help will be appreciated. Thanks.

Upvotes: 1

Views: 674

Answers (1)

RoiZentner
RoiZentner

Reputation: 163

@DateFrom is a varchar while DATEADD function returns smalldatetime.

You also try to compare the 'date' column in your CTE with @DateTo which is also a varchar.

Try explicitly matching the data types of your parameters and other columns.

Upvotes: 1

Related Questions