Table transformation to return rows within last 7 days

[Green_ Desmond]

I have a simple table where the first field is 'Date' and I enter relevant date values using the calendar control.

I want to be able to filter the table to only show rows from the past 7 days. In other words, >= GetDate()-7.

I've tried everything to make this work in a table transformer macro. At best, it returns rows from completely different months. For example, at 20 November, the filter shows rows from 13 to 20 November, but also rows for 13 to 20 October, and September, etc.!

I can use a table filter, with a column filter, by date to simply filter on from -7d to today. 

But why can't I get this to work in what should be a more flexible table transformer macro?

I've tried this:

SELECT * FROM T1 WHERE FORMATDATE(T1.'Date')
BETWEEN FORMATDATE(DATEADD(DAY, -7, GETDATE())) AND
FORMATDATE(DATEADD(DAY, 0, GETDATE()))

And this:

SELECT * FROM T1 WHERE 
T1.'Date'::Date->toLocaleDateString("en-us", { year:"numeric", month:"short", day:"2-digit"})
BETWEEN DATEADD(w,-1, GETDATE()::Date->toLocaleDateString("en-us", { year:"numeric", month:"short", day:"2-digit"}))
AND GETDATE()::Date->toLocaleDateString("en-us", { year:"numeric", month:"short", day:"2-digit"})

And also many other things besides!

It's a really simple use case. How do I get this to work? 

[Stiltsoft support · Accepted solution]

Hi @Green_ Desmond ,

Please try the following SQL query:

SELECT * FROM T*
WHERE DATEDIFF(day,'Date',"today") <= 7

The date format in the Settings tab of your Table Transformer macro should correspond your current date format that is "d M yy".

[Green_ Desmond]

That's perfect, I looked at DATEDIFF but didn't feel quite right for what I was trying to do.

But it's exactly what I want and works perfectly - thank you!