Subtracting numeric from date in Postgresql

Question

When I execute these script, it works in postgresql;

select '2021-09-01'::Date - 1

select current_date -1

But when I use this type of subtracting in function, postgresql gives me error like this;

Function usage in declare part :

v_date date := p_date - p_number;
ERROR:  operator does not exist: date - numeric

The function will not take a decimal number as input, so I don't have any expectations regarding 3.14. So "v_date date := p_date - p_number::int;" works for me. — stacks
– stacks, Commented Oct 7, 2021 at 14:23
The cleaner solution is then to declare the parameter as int — user330315
– user330315, Commented Oct 7, 2021 at 14:24

Adrian Klaver · Accepted Answer · 2021-10-07 14:18:26Z

1

Try:

v_date date := p_date - p_number::int;.

Per Date/Time Operators you can only subtract an integer, interval or date from a date.

Note: the ::int cast will round the numeric number.

UPDATE

Another option is to make p_number be an integer from the start, though this depends on whether it is being used for other purposes where it needs to be numeric.

edited Oct 7, 2021 at 14:18

answered Oct 7, 2021 at 13:45

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Klaver Over a year ago

See UPDATE for an alternative solution.

Collectives™ on Stack Overflow

Subtracting numeric from date in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related