Types don't match between the anchor and the recursive

Question

I have problem with sql query error with recursive dates

Types don't match between the anchor and the recursive part in column "CalendarDate" of recursive query "Dates".'

";WITH Dates as ("
       + " SELECT @FromDate as CalendarDate "
       + " UNION ALL "
       + " SELECT dateadd(MONTH, 1, CalendarDate) AS CalendarDate "
       + " FROM Dates "
       + " WHERE DATEADD(MONTH, 1, CalendarDate) < @ToDate   )"
       + " ,cteMonthEnd AS    ( "
       + "  SELECT* , MonthEnd = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, CalendarDate) + 1, 0)) "
       + "  FROM Dates    ) "
       + " SELECT CTE.CalendarDate, s.StrainId,GOH.CagePerDiem, "
       + " COUNT(CASE WHEN DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and M.DeathDate IS NULL  THEN 1 END) TotalKeptMicesOver17Days,"
       + " COUNT(CASE WHEN AnimalUseCd = 2 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17 and DeathDate IS NULL  THEN 1 END) BreedingKeptMices,"
       + " COUNT(CASE WHEN AnimalUseCd = 3 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and DeathDate IS NULL  THEN 1 END) ExperimentKeptMices,"
       + " COUNT(CASE WHEN AnimalUseCd = 0 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and DeathDate IS NULL  THEN 1 END) AvailableKeptMices,"
       + " COUNT(CASE WHEN AnimalUseCd = 2 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and GenderCd = 2   and DeathDate IS NULL  THEN 1 END) BreedingKeptFemaleCount,"
       + " COUNT(CASE WHEN AnimalUseCd = 2 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and GenderCd = 1   and  DeathDate IS NULL  THEN 1 END) BreedingKeptMaleCount,"
       + " COUNT(CASE WHEN AnimalUseCd = 3 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and GenderCd = 2   and  DeathDate IS NULL  THEN 1 END) ExperimentKeptFemaleCount,"
       + " COUNT(CASE WHEN AnimalUseCd = 3 and DATEDIFF(DAY, M.BirthDate, CTE.MonthEnd) >= 17  and GenderCd = 1   and  DeathDate IS NULL  THEN 1 END) ExperimentKeptMaleCount"
       + " FROM cteMonthEnd CTE"
       + " CROSS JOIN Strains S"
       + " JOIN Mice M ON M.StrainId = S.StrainId"
       + " LEFT JOIN GroupOverhead GOH on S.GroupId = GOH.GroupId"
       + " WHERE  S.GroupId in (Select GroupId from Groups where OrganizationId = @groupId)"
       + " GROUP BY S.StrainId, CalendarDate,GOH.CagePerDiem"
       + " order by CalendarDate asc";

this query already executed in stored procedure but we have problem with this code will be merge in c# code i got error mentioned on above

Can you post the SQL query that worked in SSMS before you tried to convert to c# string? I pretty good at doing this type of conversion. — jdweng, Jan 04 '19 at 11:50
Probably you have changed the datatype of the `@FromDate` parameter when converting to C# — Martin Smith, Jan 04 '19 at 12:34

score 0 · Answer 1 · answered Jan 04 '19 at 12:09

You aren't specifying the data type of @FromDate in the only column from the first result set (the anchor). The first SELECT on a UNION should specify correctly the data type so you don't have this clashing problems.

Cast the variable to the correct data type, for example DATE:

SELECT CONVERT(DATE, @FromDate) as CalendarDate
UNION ALL --...

Types don't match between the anchor and the recursive

1 Answers1