I can't seem to find any documentation about how to do this in Data Productivity Cloud, there is documentation for Matillion ETL but that does not seem to work.
I'd like to set the StartDate and EndDate variables in my pipeline to dynamically assign the date range for my API endpoint. I have these variables created as VARCHAR types. Then in my Orchestration pipeline I use 'Update Scalar' to create the new timestamp value.
To select yesterday's date in SQL I would use this:
DATEADD(DAY,-1,CURRENT_TIMESTAMP)
I don't see any Matillion documentation that describes how to accomplish this. Anyone have thoughts about this? It seems like a very basic task and should be very well documented by now.
I did eventually find some documentation regarding this but simply said to write with javascript code. This almost worked for me but not quite. I was trying to get the current timestamp and convert to my local timezone AND display in ISO format. I've only figured out how to do one of these conversions, attempting to apply a second fails.
In the end, it was recommended by one of Matillion's architects to query Snowflake to get the date. This is much easier but does require going external from Matillion to accomplish what should be a relatively simple task.
I'm closing this issue.
Hi @mick.jaeger and thanks for sharing the answer that you found! If it's of interest, you could add an idea to our Ideas Portal to add this within the product as an improvement. Other members of the community can then vote on it and the team will take a further look.
Thanks again!
Claire