Snowflake Function input is datetime, but when i call the function it's not quite working

Question

with temp  AS (
    SELECT CAST(SUM(sreg.ScrapQuantity) AS INT) AS Quantity,
        sreas.Name AS ScrapReason,
        to_date((DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')))) AS DATETIME,
        sreg.EquipmentID AS EquipmentID
    FROM ScrapRegistration sreg
    INNER JOIN ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')),
        sreg.EquipmentID,
        sreas.Name
    )
    
select temp.EquipmentID
from RAW_CPMS_AAR.equipment e, temp
Where e.ID = (select * from table(cfn_GetShiftIDFromDateTime_test(temp.DateTime::DATETIME, 0))) --this works with datetime

when i run this query above, i get Processing aborted due to error 300010:391167117; incident 3245754.. I believe this is an issue with the temp.datetime -- when i run the same codebut hardcoding the function input, i get the desired output.

with temp  AS (
    SELECT CAST(SUM(sreg.ScrapQuantity) AS INT) AS Quantity,
        sreas.Name AS ScrapReason,
        to_date((DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')))) AS DATETIME,
        sreg.EquipmentID AS EquipmentID
    FROM ScrapRegistration sreg
    INNER JOIN ScrapReason sreas ON sreas.ID = sreg.ScrapReasonID
    INNER JOIN WorkRequest wr ON wr.ID = sreg.WorkRequestID
    INNER JOIN SegmentRequirementEquipmentRequirement srer ON srer.SegmentRequirementID = wr.SegmentRequirementID
    GROUP BY DATEADD(MINUTE, 30 * (DATE_PART(MINUTE, sreg.ScrapTime) / 30), DATEADD(HOUR, TIMESTAMPDIFF(HOUR, '0', sreg.ScrapTime), '0')),
        sreg.EquipmentID,
        sreas.Name
    )
    
select temp.EquipmentID
from RAW_CPMS_AAR.equipment e, temp
Where e.ID = (select * from table(cfn_GetShiftIDFromDateTime_test('2021-12-02 10:03:0.00'::datetime, 0))) --this works with datetime

it seems that that somwehere along the line, it's not liking the date format i put in. it's not returning me an error of not liking the input.

here is the function.

CREATE OR REPLACE FUNCTION DB_BI_DEV.RAW_CPMS_AAR.cfn_GetShiftIDFromDateTime (dateTime TIMESTAMP_NTZ(9), shiftCalendarID int)

RETURNS table (shiftID int)
AS
$$
WITH T0 (ShiftCalendarID, CurDay, PrvDay)
AS (
    SELECT TOP 1
        ID AS ShiftCalendarID,
        DATEDIFF( day, BeginDate, dateTime ) % PeriodInDays + 1 AS CurDay,
        ( CurDay + PeriodInDays - 2 ) % PeriodInDays + 1 AS PrvDay
    FROM RAW_CPMS_AAR.ShiftCalendar
    WHERE ID = shiftCalendarID
        OR (    shiftCalendarID IS NULL
            AND Name = 'Factory'
            AND BeginDate <= dateTime )
    ORDER BY BeginDate DESC
),
T1 (TimeValue)
AS (
    SELECT TIME_FROM_PARTS(
        EXTRACT(HOUR   FROM dateTime),
        EXTRACT(MINUTE FROM dateTime),
        EXTRACT(SECOND FROM dateTime))
    )

SELECT ID as shiftID
    FROM RAW_CPMS_AAR.Shift, T0, T1
    WHERE Shift.ShiftCalendarID = T0.ShiftCalendarID
    AND (  ( FromDay = T0.CurDay AND FromTimeOfDay <= T1.TimeValue AND TillTimeOfDay >  T1.TimeValue )
            OR ( FromDay = T0.CurDay AND FromTimeOfDay >= TillTimeOfDay AND FromTimeOfDay <= T1.TimeValue )
            OR ( FromDay = T0.PrvDay AND FromTimeOfDay >= TillTimeOfDay AND TillTimeOfDay >  T1.TimeValue )
        )
$$
;