SQL Server update caused Datediff to not work

Question

I know there were several articles about this topic already and I went through them but I still am not able to get this to run properly. I also used DATEDIFF_BIG, but then I get this error:

DATEDIFF_BIG is not a recognized built-in function name.

This used to be able to run but I think there was a SQL server update and now all of the TAT queries are breaking.

SELECT
    MSTR.FIRST_OF_MTH AS [MONTH],
    COALESCE(R.[AREA],T.[AREA]) AS [AREA],
    COALESCE(R.NUM_REQUESTS,0) AS NUM_REQUESTS,
    COALESCE(R.DURATION_REQ,0) AS DURATION_REQ,
    COALESCE(T.NUM_TRANS,0) AS NUM_TRANS,
    COALESCE(T.DURATION_TRANS,0) AS DURATION_TRANS
FROM
        (
        SELECT FIRST_OF_MTH
        FROM [EPICDW].[DBO].[DATE_EXT]
        WHERE (YEAR([DATE_EXT].[THEDATE]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE()))
        GROUP BY FIRST_OF_MTH
        ) MSTR
LEFT JOIN 
        (
        --transactions
        SELECT
                DATEADD(month, DATEDIFF(month, 0, [CLOSE_DT]), 0) AS [MONTH],
                [TEAM] AS AREA,
                SUM(1) AS NUM_TRANS,
                SUM(B.[Calendar_Duration]) AS DURATION_TRANS
        FROM [dbo].[v_tab_config_bl_trans_comb]
        CROSS APPLY fn_Ben_Ops_Turnaround ([OPEN_DT], [CLOSE_DT]) B
        WHERE Year([CLOSE_DT]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE())
        GROUP BY
                DATEADD(month, DATEDIFF(month, 0, [CLOSE_DT]), 0),
                [TEAM]
        ) T
        ON MSTR.FIRST_OF_MTH = T.[MONTH]
LEFT JOIN 
        (
        --requests
        SELECT 
                DATEADD(month, DATEDIFF(month, 0, [CLOSED_DT]), 0) AS [MONTH],
                TEAM AS AREA,
                SUM(1) AS NUM_REQUESTS,
                SUM([DUR_CAL]) AS DURATION_REQ
          FROM [dbo].[v_tab_config_requests]
          WHERE year([CLOSED_DT]) BETWEEN YEAR(GETDATE()) - 1 AND YEAR(GETDATE())
        GROUP BY DATEADD(month, DATEDIFF(month, 0, [CLOSED_DT]), 0),        TEAM
        ) R
ON MSTR.FIRST_OF_MTH = R.[MONTH]
AND T.AREA = R.AREA

question from:https://stackoverflow.com/questions/65835968/sql-server-update-caused-datediff-to-not-work

Answer 1

Waitting for answers