First and last day of last month in Redshift

Question

I want to find the first and last day of last month in Redshift. However, most of the postgresql features like date_trunc do not seem to work. How can I achieve that?

Answer 1

In Redshift, you can use date_trunc() (See online documentation).

For the first day of last month:

select date_trunc('month', current_date) - interval '1 month'

Or:

select date_add(month, -1, date_trunc('month', current_date))

For the last day of last month:

select date_trunc('month', current_date) - interval '1 day'

Answer 2

Or for last day in moth you can use a LAST_DAY Function. :) http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

select last_day(current_date)