How to filter date range from "date" - 2 weeks and "date" + 2 weeks

1.4k views Asked by At

I have a table t like:

VALID_FROM | VALID_TO   | stock
2020-10-01 | 2020-10-02 | 10
2020-10-02 | 2020-10-04 | 9
2020-10-04 | 2020-10-08 | 5
...        | ...        |
2020-12-15 | 2020-12-16 | 0

I need to set a date range for a specific date from:

'2020-11-26' - 2 weeks / 14 days '2020-11-26' + 2 weeks / 14 days

I could just use fixed dates, e.g.

SELECT *
FROM table AS t
WHERE t.VALID_FROM >= '2020-11-12'
AND t.VALID_TO <= '2020-12-10'

But the date range may change and I would like to just add days or weeks without having to use a calendar every time.

So something like:

SELECT *
FROM table AS t
WHERE t.VALID_FROM >= '2020-11-26' - INTERVAL '14' DAY
AND t.VALID_TO <= '2020-11-26' + INTERVAL '14' DAY

But that gives me the error message:

Feature not supported: Cannot cast from INTERVAL DAY(2) TO SECOND(3) to DOUBLE

1

There are 1 answers

1
Gordon Linoff On BEST ANSWER

Try using the add_days() function:

WHERE t.VALID_FROM >= add_days('2020-11-26', -14) AND
      t.VALID_TO <= add_days('2020-11-26', -14)