Add Data that has been modified in the last 7 days

Options
apatten
apatten Member
in Magic ETL

I have a snowflake dataset that has a modified date column. I want to merge the data that has been modified in the last 7 days to the dataset. Sometimes an order is modified after its placed and we want to capture the new values of the order.

I have used this query but its not working

SELECT * FROM "TABLE" WHERE "ORDER_MODIFIED_TIME" > SUBDATE(CURDATE(), interval 7 day)

Any help would be appreciated

Tagged:

Comments

  • ST_Superman
    ST_Superman Domo Employee

    At first glance, that seems like it should work. What is the data type for ORDER_MODIFIED_TIME ?

    I think you need to use the back tick ` instead of a double quote " as well.

  • apatten
    apatten Member

    ORDER_MODIFIED_TIME is in a date time format (yyyy-mm-dd hh:mm:ss) .

    I will try using the back tick too.

  • RobSomers
    RobSomers Coach

    Is the problem that you're getting an error, or is the where clause not working as expected?

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • apatten
    apatten Member

    I have run it with a few different variations of this query so I have gotten a few errors.

    1. CURDATE is not a valid function
    2. Unexpected value of '7', meaning the 7 day interval
    3. Invalid identifier with 'interval'
    4. Unexpected with 'day'

    I have tried variations with a BETWEEN function, ADDDATE, SUBDATE and GETDATE

  • RobSomers
    RobSomers Coach

    Are you running this query outside of Domo, or are you using it somewhere in Magic ETL?

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • apatten
    apatten Member

    This is as I am pulling the data from snowflake to Domo. In the dataset configuration within Domo

  • GrantSmith
    GrantSmith Coach

    Snowflake has a bit different syntax. Try something like:

    SELECT * FROM "TABLE" WHERE "ORDER_MODIFIED_TIME" > CURENT_DATE() - interval '7 day'
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • apatten
    apatten Member

    That seems to have worked! Thank you!

Categories