Типы не совпадают между якорем и рекурсивом

Question

У меня проблема с SQL-запросом ошибка с рекурсивными датами

Типы не совпадают между якорем и рекурсивной частью в столбце "CalendarDate" рекурсивного запроса "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";

этот запрос уже выполнен в хранимой процедуре, но у нас есть проблема с этим кодом будет слияние в коде C# я получил ошибку, упомянутую выше

Answer 1

Вы не указываете тип данных @FromDate в единственном столбце из первого набора результатов (якорь). Первый SELECT на UNION должен правильно указать тип данных, чтобы у вас не возникало проблем с конфликтами.

Приведите переменную к правильному типу данных, например DATE:

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