First and last day of last month in Redshift

sql amazon-redshift
Nithin Chandy picture Nithin Chandy · Aug 30, 2016 · Viewed 22.3k times · Source

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

Gordon Linoff picture Gordon Linoff · Aug 30, 2016

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'

Related questions

postgresql - view schema privileges

Is there a query I can run to show currently assigned privileges on a particular schema? i.e. privileges that were assigned like so: GRANT USAGE ON SCHEMA dbo TO MyUser I have tried SELECT * FROM information_schema.usage_privileges; …

Read More
How do I view grants on Redshift

I'd like to view grants on redshifts. I found this view for postgres: CREATE OR REPLACE VIEW view_all_grants AS SELECT use.usename as subject, nsp.nspname as namespace, c.relname as item, c.relkind as type, use2.usename …

Read More
How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using to_date("act_dt",'YYYYMMDD') AS "act date" …

Read More