First day of the month function?

Moderator: NorbertKrupa

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

First day of the month function?

Post by beth » Tue Oct 08, 2013 8:30 pm

Hi y'all,

I found a last_day() function, but not a first_day() function. Does one exist? How can I find the first day of the month for a given date?

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Re: First day of the month function?

Post by beth » Tue Oct 08, 2013 8:32 pm

Never mind, I think I figured this out myself :oops:

Code: Select all

dbadmin=> select last_day('2013-08-10') + 1 as first_day;
 first_day  
------------
 2013-09-01
(1 row)

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: First day of the month function?

Post by id10t » Tue Oct 08, 2013 8:36 pm

HI!

FYI: your solution gives NEXT month

Code: Select all

daniel=> select date(date_trunc('MONTH', now()));
    date    
------------
 2013-10-01
(1 row)

daniel=> select to_date('2013-08-10', 'YYYY-MM-01');
  to_date   
------------
 2013-08-01
(1 row)

daniel=> select to_date(now()::varchar, 'YYYY-MM-01');
  to_date   
------------
 2013-10-01
(1 row)

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Re: First day of the month function?

Post by beth » Wed Oct 09, 2013 1:34 pm

Yikes! Thanks for noticing my error sKwa :lol: I'll use the your solution.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: First day of the month function?

Post by JimKnicely » Wed Oct 09, 2013 2:09 pm

beth,

Don't forget you can create your own function too!

(using one of sKwa's solution):

Code: Select all

dbadmin=> CREATE FUNCTION first_day(x DATE) RETURN DATE
dbadmin-> AS
dbadmin-> BEGIN
dbadmin-> RETURN DATE(DATE_TRUNC('MONTH', x));
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT now()::date now, first_day(now()::date) first_day;
    now     | first_day
------------+------------
 2013-10-09 | 2013-10-01
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: First day of the month function?

Post by id10t » Wed Oct 09, 2013 2:16 pm

Hi!

[DELETED]
Last edited by id10t on Sat May 09, 2015 3:34 pm, edited 1 time in total.

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Re: First day of the month function?

Post by beth » Wed Oct 09, 2013 2:31 pm

Cool, thanks guys! Are you brothers or something :)

Post Reply

Return to “Vertica SQL Functions”