CODEWITHSUNDEEP
sqlamazon-redshift
kmathers
kmathers

Reputation: 1

Trying to get the first day of last month, need a Postgresql implementation

I need to calculate the first day of last month, and the last day of last month as part of a SQL query, I found the exact answer to what I am looking for in this post

for instance 

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) --First day of previous month

While I can follow the logic and it seems to work in SQL Server, I am using Postgresql/Redshift and I am getting the error 

[42883][500310] [Amazon](500310) Invalid operation: function          
pg_catalog.date_diff("unknown", integer, timestamp without time zone) does 
not exist;

Can someone explain to me why Postgresql is throwing an error and how I can modify the code to get the same solution in Postgres?

Upvotes: 0

Views: 2562

Answers (2)

kmathers
kmathers

Reputation: 1

Second edit: okay I finally have a solution. This works in postgresql

BETWEEN  DATEADD(days, (DATEPART(day, CURRENT_DATE) - 1), DATEADD(month, 
-1, CURRENT_DATE)) AND  DATEADD(days, (DATEPART(day, CURRENT_DATE) - 1), 
CURRENT_DATE)

I figured it out. You can't put an int in the date field of a datediff or dateadd in Postgresql. If you take the SQL Server solution from above and replace the 0's in each function with two of the same date, it doesn't matter what date it is but they have to be the same, it will produce the desired output.

edit: Actually this only helps get the first of last month, not the last of last month, as I haven't figured out a way to replicate the purpose of the -1 included in the second SQL script in the link above. I can't replace this int with an equivalent date. Help would still be appreciated.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656824

Simpler in Postgres: Use date_trunc() to get the first day of the month, and then ...

  • subtract "one month" for the first day of the last month.
  • or subtract "one day" for the last day of the last month.
SELECT date_trunc('month', now()) - interval '1 month' AS last_month_first_day
     , date_trunc('month', now()) - interval '1 day'   AS last_month_last_day;

Returns timestamp or timestamptz, depending on input. timestamptz for now() as input.

To return type date:

SELECT (date_trunc('month', now()) - interval '1 month')::date AS last_month_first_day
     , (date_trunc('month', now()))::date - 1                  AS last_month_last_day;

You can subtract integer from a date (but not from a timestamp) to subtract days.

db<>fiddle here

Related:

Upvotes: 2

Related Questions