Queries: Using multiple columns with same name but from different tables as conditions for the WHERE clause?

Question

I want to use SOME_DATE and ANOTHER_DATE columns from different tables as part of the conditions for the WHERE clause of my query.

I can only get it to work if I remove AND ATT.[SOME_DATE] <= '02 december 2020' AND ATT.[ANOTHER_DATE] >= '02 december 2020' from the query below:

;WITH cs AS 
( 
    SELECT 
        t.LKJ_UID,
        t.ITEM_UID,
        t.[I_T_UID], 
        t.[I_I_CD], 
        t.[L_DS], 
        t.[SOME_DATE], 
        t.[ANOTHER_DATE], 
        t.[AN_DATED], 
        t.[LS_E_DATE], 
        t.[LS_E_UST_UID], 
        t.[PTU_UID], 
        t.[FMT_UID], 
        t.[DG_LT], 
        t.[DG_A], 
        t.[OD], 
        t.[NTH], 
        t.[BJU], 
        t.[BJI], 
        t.[LOK], 
        t.[JUT], 
        ATT.[FG_UID], 
        ATT.[RTU_UID], 
        ATT.[LKJ_CD], 
        ATT.[LKJ_NAME], 
        ATT.[LKJ_TYPE_UID], 
        ATT.[LKJ_LENGTH], 
        ATT.[LCA_KIP_UID], 
        ATT.[CL_WD], 
        ATT.[LCA_OCC_LC_UID], 
        ATT.[SJ_K], 
        ATT.[SJ_J],
        ATT.SOME_DATE as SA_SOME_DATE,
        ATT.ANOTHER_DATE as SA_ANOTHER_DATE
       
    FROM 
        CATT AS t
    LEFT OUTER JOIN 
        ATT ON t.LKJ_uid = ATT.LKJ_uid
    
    WHERE 
        t.[SOME_DATE] <= '02 december 2020'
        AND t.[ANOTHER_DATE] >= '02 december 2020'
        AND ATT.[SOME_DATE] <= '02 december 2020'
        AND ATT.[ANOTHER_DATE] >= '02 december 2020'

    ORDER BY t.[ITEM_UID]
        OFFSET 24 * 0 ROWS
    FETCH NEXT 24 ROWS ONLY
) 

SELECT *
FROM cs
ORDER BY 
cs.ITEM_UID

Only if I remove this part of the last example will it work, but I need it to obey all 4 conditions, not just the two:

AND ATT.[SOME_DATE] <= '02 december 2020'
AND ATT.[ANOTHER_DATE] >= '02 december 2020'

Thank you for your help

EDIT: (in response to Shawnt00's answer)

This query returns 72k rows, whereas the changes you suggested return 1.6 million records:

SELECT dbo.CATT.*, 
       dbo.ATT.* 
FROM   dbo.CATT 
       LEFT OUTER JOIN dbo.ATT 
               ON dbo.CATT.LKJ_uid = 
                  dbo.ATT.LKJ_uid 
WHERE  ( dbo.CATT.SOME_DATE <= 
         CONVERT(DATETIME, '02 december 2020' 
                , 
                102) ) 
       AND ( dbo.CATT.ANOTHER_DATE >= 
             CONVERT(DATETIME, '02 december 2020', 
                 102) ) 
       AND ( dbo.ATT.SOME_DATE <= 
             CONVERT(DATETIME, '02 december 2020', 
                 102) ) 
       AND ( dbo.ATT.ANOTHER_DATE >= 
             CONVERT(DATETIME, '02 december 2020', 
             102) )

Answer 1

Move the condition into the join. Remove it from where.

LEFT OUTER JOIN 
        section_attributes ON t.section_uid = section_attributes.section_uid
    AND SECTION_ATTRIBUTES.START_DATE <= '02 december 2020'
    AND SECTION_ATTRIBUTES.END_DATE >= '02 december 2020'