The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

What's the first day of the month?

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.