What's the first day of the month?

[Deleted User]

This tip was authored by Jim Knicely.

In the last Vertica Quick Tip, you learned about the Vertica built-in LAST_DAY function. So where’s the FIRST_DAY function? You’ll have to create your own.

So how do you compute the first day of the month for a given date? There are many ways, each involving a lot of typing.

Example:

dbadmin=> SELECT (EXTRACT(MONTH FROM '09/19/2018'::date) || '/1/' || EXTRACT(YEAR FROM '09/19/2018'::date))::date FIRST_DAY;
FIRST_DAY
------------
2018-09-01
(1 row)

dbadmin=> SELECT LAST_DAY(ADD_MONTHS('09/19/2018', -1)) + 1 FIRST_DAY;
FIRST_DAY
------------
2018-09-01
(1 row)

dbadmin=> SELECT DATE_TRUNC('MONTH', '09/19/2018'::TIMESTAMP)::DATE;
DATE_TRUNC
------------
2018-09-01
(1 row)

I’ve found that the DATE_TRUNC method performs best so use it for your user-defined function:

dbadmin=> CREATE OR REPLACE FUNCTION first_day (x DATE) RETURN DATE
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN DATE_TRUNC('MONTH', x)::DATE;
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT first_day('09/19/2018') FIRST_DAY;
FIRST_DAY
------------
2018-09-01
(1 row)

Have Fun!