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

Posted March 1, 2018 by Soniya Shah, Information Developer

Rear view of two partially unrecognizable men sitting in front of a computer. One of them is pointing at a screen where are several lines of computer code.
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!