What's the first day of the month?

SarahLSarahL Administrator, Moderator, Employee, Registered User, VerticaExpert, CABuser

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!

Sign In or Register to comment.