Amazon Redshift - How to extract previous month data

Question

I am trying to automate a report that runs at the beginning of each month to query out all sales for the previous month. Given below is the query I am using but it returns null value. Could anyone help me with what I am doing wrong here.

I am using Amazon Redshift

select created_at,sale_id from billing where to_char(created_at,'yyyy-mm') = EXTRACT(month FROM CURRENT_DATE - '1 month'::interval);

Thanks.

user330315 · Accepted Answer · 2018-05-14 07:32:15Z

You are comparing a string like '2018-05' to a numeric value like 5 (extract returns a single number) so that is not going to work. You need to use the same values on both sides of the equal sign, e.g.

select created_at,sale_id from billing where to_char(created_at,'yyyy-mm') = to_char(CURRENT_DATE - '1 month'::interval, 'yyyy-mm');

Collectives™ on Stack Overflow

Amazon Redshift - How to extract previous month data

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related