0

I am looking for a way to replace EOMONTH in PostgreSQL. I have a db with a date column. I have a script I want to run on the first of the month that will select only the rows that fall between last months first day and last day.

I need the equivalent of this:

SELECT * 
FROM database 
WHERE dateCol BETWEEN SYMMETRIC EOMONTH(GETDATE(),-2)+1 AND EOMONTH(GETDATE(),-1);

I am very new to SQL, so let me know if something doesn't make sense. Thank you!

NMALM
  • 378
  • 2
  • 19
  • What exactly does `EOMONTH()` do? –  Nov 04 '19 at 21:46
  • @a_horse_with_no_name https://learn.microsoft.com/de-de/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver15 Last day of month of first argument. If second argument present, first add that number of month to the first argument and then calculate end of month – Islingre Nov 04 '19 at 21:47
  • 1
    I woul have a look at the ```date_trunc()``` function: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC – Islingre Nov 04 '19 at 21:48

1 Answers1

1

This SQL Server expression:

EOMONTH(GETDATE(),-2)+1

Means: last day of month M-2 (we are in November, so this means September 30th)

One way to express this in postgres is:

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

Which means: truncate the current date to the first day of the month, then go back 1 month and one day.

Demo on DB Fiddle

GMB
  • 216,147
  • 25
  • 84
  • 135