Vertica Quick Tip: What’s the first day of the month?

This blog post was authored by Jim Knicely.

In the last Vertica Quick Tip we 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 let’s use it for our 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!