Page 1 of 2
First day of the month function?
Posted: Tue Oct 08, 2013 8:30 pm
by beth
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?
Re: First day of the month function?
Posted: Tue Oct 08, 2013 8:32 pm
by beth
Never mind, I think I figured this out myself
Code: Select all
dbadmin=> select last_day('2013-08-10') + 1 as first_day;
first_day
------------
2013-09-01
(1 row)
Re: First day of the month function?
Posted: Tue Oct 08, 2013 8:36 pm
by id10t
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)
Re: First day of the month function?
Posted: Wed Oct 09, 2013 1:34 pm
by beth
Yikes! Thanks for noticing my error sKwa
I'll use the your solution.
Re: First day of the month function?
Posted: Wed Oct 09, 2013 2:09 pm
by JimKnicely
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)
Re: First day of the month function?
Posted: Wed Oct 09, 2013 2:16 pm
by id10t
Hi!
[DELETED]
Re: First day of the month function?
Posted: Wed Oct 09, 2013 2:31 pm
by beth
Cool, thanks guys! Are you brothers or something