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

Posted March 1, 2018 by Soniya Shah, Information Developer

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!