0

Suppose I have a date column called hire_date in the following format YYYY-MM-DD. I want to truncate the date so that the last day of the month is shown. For example, a record with 2015-01-11 is shown as 2015-01-31.

I know that I can use DATE_TRUNC('month', hire_date) to get a timestamp of the beginning of the month but what about the end of the month?

The answer selected here How to get the last day of month in postgres? states to use the last_day() function but I've tried LAST_DAY(hire_date) and I get an error saying function last_day(date) does not exist.

Is there an easier way than the following:

DATE_TRUNC('month', hire_date) + INTERVAL '1 month' - INTERVAL '1 day'
Dai
  • 141,631
  • 28
  • 261
  • 374
IamWarmduscher
  • 875
  • 2
  • 10
  • 27
  • last_day is not a PG function. Instead you could convert your own solution into a function – KESO Aug 24 '21 at 02:47
  • 1
    In databases, date columns don't have "formats". – Dai Aug 24 '21 at 02:49
  • "I want to truncate the date so that the last day of the month is shown" - **that is not what "truncate" means**. – Dai Aug 24 '21 at 02:49
  • @Dai Do you have a solution besides the calculation at the bottom of my post? – IamWarmduscher Aug 24 '21 at 02:54
  • 1
    No, and your question _is the same_ as the question you linked to - just use the non-Redshift-specific answer. Also, some general advice: when working with date-ranges it's far better to use **exclusive upper-bounds** than inclusive-upper-bounds, that way you can avoid your problem entirely. – Dai Aug 24 '21 at 02:57
  • @Dai No, because that's the same link I provided in my post. – IamWarmduscher Aug 24 '21 at 02:57
  • The "does this answer your question" comment is automatically added by SO whenever users make a vote-to-close-because-duplicate. – Dai Aug 24 '21 at 02:58
  • @Dai Please don't mark my question as having been answered. My question is, "is there an easier way than the following." Surely Postgres has a function like EOMONTH() in MS SQL. – IamWarmduscher Aug 24 '21 at 02:58
  • 1
    @IamWarmduscher That sub-question of yours ("is there an easier way...?") **is** answered by that question - and the answer is "no" - I suggest you **read all the answers** posted there. The best alternative is to write a scalar UDF - or even better: not using inclusive-upper-bounds for date-ranges. – Dai Aug 24 '21 at 03:00

0 Answers0