Add Data that has been modified in the last 7 days

apatten · 2022-06-28T20:50:27+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

SQL

Date

Snowflake

Comments

ST_Superman

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

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

I will try using the back tick too.

RobSomers

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

apatten

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

CURDATE is not a valid function
Unexpected value of '7', meaning the 7 day interval
Invalid identifier with 'interval'
Unexpected with 'day'

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

RobSomers

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

apatten

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

GrantSmith

Snowflake has a bit different syntax. Try something like:

SELECT * FROM "TABLE" WHERE "ORDER_MODIFIED_TIME" > CURENT_DATE() - interval '7 day'

apatten

That seems to have worked! Thank you!