Snowflake SQL Query - Invalid Argument Function Using DATEADD()-CodePudding

I have the below SQL query I am using in Snowflake where, if the day of the week = Monday, I need to subtract 48 hours from 'HOURS_SINCE_UPDATE':

SELECT DAYNAME(GetDate()) AS DAY,
    INC_PUBLIC.INCIDENT_NUMBER,
    INC_PUBLIC.COMPANY,
    INC_PUBLIC.ASSIGNED_GROUP,
    INC_PUBLIC.ASSIGNEE_FULL_NAME,
    INC_PUBLIC.ASSIGNEE_ID,
    INC_PUBLIC.SUMMARY,
    INC_PUBLIC.SUBMIT_DATE_TIME,
    INC_PUBLIC.TARGET_DATE_TIME,
    INC_PUBLIC.STATUS,
    WORK_LOGS.WORK_INFO_TYPE,
    WORK_LOGS.SUMMARY,
    WORK_LOGS.NOTES,        
    CASE
        WHEN WORK_LOGS.SUBMIT_DATE_TIME IS NULL THEN INC_PUBLIC.SUBMIT_DATE_TIME
        ELSE WORK_LOGS.SUBMIT_DATE_TIME
    END AS WL_SUBMIT_DATE_TIME,        
    CASE
        WHEN DAY = 'Mon' then DATEADD(HOUR, -48, DATEDIFF(HOURS, WL_SUBMIT_DATE_TIME, GETDATE()))
        ELSE DATEDIFF(HOURS, WL_SUBMIT_DATE_TIME, GETDATE())
    END AS HOURS_SINCE_UPDATE,   
    ROW_NUMBER() OVER (PARTITION BY INC_PUBLIC.INCIDENT_NUMBER ORDER BY WORK_LOGS.SUBMIT_DATE_TIME desc) RW,
    CASE
        WHEN INC_PUBLIC.TARGET_DATE_TIME IS NULL THEN 'Target date is blank'
        WHEN TARGET_DATE_TIME < GETDATE() THEN 'Target date expired'
        WHEN HOURS_SINCE_UPDATE > 48 THEN 'Overdue for an update'
        WHEN INC_PUBLIC.STATUS = 'Assigned' THEN 'Ticket in assigned status'
        ELSE 'Good'
    END AS REASON_ON_REPORT
    FROM SFDW_PROD.SERVICE_MANAGEMENT.INCIDENT_PUBLIC AS INC_PUBLIC
    LEFT JOIN 
        (
            SELECT INCIDENT_NUMBER, SUBMIT_DATE_TIME, WORK_INFO_TYPE, SUMMARY, NOTES
            FROM SFDW_PROD.SERVICE_MANAGEMENT.INCIDENT_WORK_INFO_PUBLIC
            WHERE SUMMARY NOT IN ('Email-Inbound', 'TicketLogger Classification', 'APPMETADATA', 'Auto-Triage Assignment')
            AND WORK_INFO_TYPE IN ('Email System', 'Associate Phone Communication','IM Communication','Customer Phone Communication', 'Working Log')
        ) AS WORK_LOGS
    ON INC_PUBLIC.INCIDENT_NUMBER = WORK_LOGS.INCIDENT_NUMBER
    WHERE (INC_PUBLIC.ASSIGNED_GROUP LIKE 'DS$_%' ESCAPE '$' AND INC_PUBLIC.STATUS_CODE IN (1,2,3) AND INC_PUBLIC.ASSIGNEE_FULL_NAME != 'NS')
    ORDER BY INC_PUBLIC.INCIDENT_NUMBER, WORK_LOGS.SUBMIT_DATE_TIME
    limit 100;

CASE
    WHEN DAYNAME(GetDate()) = 'Mon' then TIMEADD(HOURS, 48, DATEDIFF(HOUR, WORK_LOGS.SUBMIT_DATE_TIME, GETDATE()))
    ELSE DATEDIFF(HOURS, WORK_LOGS.SUBMIT_DATE_TIME, GETDATE())
END AS HOURS_SINCE_UPDATE,

I keep getting the following error:

SQL compilation error: error line 19 at position 30 Invalid argument types for function 'DATE_ADDHOURSTOTIMESTAMP': (NUMBER(2,0), NUMBER(9,0))

What am I doing wrong?

CodePudding user response：

I didn't check the whole query but the error is related to this part:

DATEADD(HOUR, -48, DATEDIFF(HOURS, WL_SUBMIT_DATE_TIME, GETDATE()))

You calculate the time difference (in hours) between WL_SUBMIT_DATE_TIME and the current date. It returns a number, not a date. Then you try to use this number with the DATEADD function, and it expects to have a date instead of a number. This is what you get this error:

If you want to subtract 48 hours, then just subtract it:

DATEDIFF(HOURS, WL_SUBMIT_DATE_TIME, GETDATE()) - 48