I am writing a Postgres procedure and I want to replace only date part of date with some static no.
eg:-
varDate Date default '2018-05-21';
Say I want to make this date as '2018-05-08';
Can anyone tell how to achieve this.
Till now what i have tried is this
varDate := varDate - interval '1 day' * 21 + interval '1 day' * 8;
The above expression gives me proper results. But is there any shortcut to change only the date part of the date.
As far as I understand you want to change the day of the month to 8.
One way to do this is to "truncate" the date to the start of the month, then add 8 days:
vardate := date_trunc('month', vardate)::date + 8;
date_trunc returns a timestamp that's why the cast ::date is needed.
Another option is to "build" a date based on the existing date:
vardate := make_date(extract(year from vardate)::int, extract(month from vardate)::int, 8);
date_trunc('month', vardate)::date + 7 if he wants the day value to be 8?Another option is to add a number of days to the date so you land on the 8th day:
select vardate::date + (8 - extract(day from vardate) * interval '1 day'
