We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


What's the first day of the month? — Vertica Forum

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.