Date range manipulation in SQL editor connecting to HANA View

[prasannaJ]

Hello everyone, I am trying to connect to HANA view and do some calculations but since my db is large the admin team has restricted the data by date fields. so i need to input date range to extract the data i want. I need to run a report everyday using rolling 30 day data. (meaning:
From: Today's date -30, TO: today's date).

This is what i did: I used "Action" function to try finding a arbitrary text variable i have given in the script using "Replace" but it isnt working, could anyone please help me with this?

('PLACEHOLDER' = ('$$Act_GI_DAte_From$$',
'20200910'), -  Here i tried doing this

('PLACEHOLDER' = ('$$Act_GI_DAte_From$$',
'Enterfromdate'),

and using "Action" i tried "Update with formula" option and tried replacing the "Enterfromdate" variable to "Datetimenow" also i did the appropriate formatting but no luck

{but nothing changes - doesn't throw any errors - just 0 records pulled)

Objective is to have 2 date ranges ( 30days from today)

[rituraj963]

Hi @prasannaJ 

I wonder if you tried updating the where clause like this :

WHERE DB_DATE_FIELD>=add_days(current_date, -1)

[Ben_H]

Hi @prasannaJ,

It does actually work if you set this up as a batch macro, and feed in the data you want to update with the action from the outside.

Regards,

Ben

[prasannaJ]

Hi rituraj963, The problem is the script is marked the date field as a placecholder - so i guess you can enter just one value and not any mathematical operations in them like  >= or <=     😞

[prasannaJ]

Hi Ben, Thank you so much for responding. Can you elaborate on the solution, i am new at this and never tried a batch macro before.

[prasannaJ · Accepted solution]

Hi All, 

I found a solution to this problem. Using dynamic input tool all i need to do is key in the daterange i wanted and input that to replace the date string. 

Find the text you wanted to replace and replace it with the date range you wanted

Hope this helps. If needed any further explanation reach out.