I don't have to remember --- postgresql does that:
select
to_char(
generate_series(
date( cast( to_char(current_date, 'YYYY' ) as integer) ||
+'-02-01' )
, date( cast( to_char(current_date, 'YYYY' ) as integer) + 1 ||
+'-01-01' )
, interval '1 month'
) - interval '1 day', 'DD')
;
to_char
---------
31
28
31
30
31
30
31
31
30
31
30
31
(12 rows)
Or, golfed down a bit...
select to_char(generate_series('20140201','20150101',interval'1mon')-i
+nterval'1','DD');
...it's shorter than the italian nursery rhyme! ;-)
update 1, yeah, I'm updating & golfing it a bit, sorry :)
update 2, (aargh! Oracle wins the Golf contest by 2 characters...
update 3, (aha! Oracle wins the Golf contest by only 1 character, (pg accepts '1mon' for '1 mon')
update 4, (aha!)
select to_char(generate_series('140201','150101',interval'1mon')-inter
+val'1','DD'); -- Pg (9.5dev)
SELECT TO_CHAR(ADD_MONTHS(DATE'14-1-1',LEVEL)-1,'DD')FROM Dual CONNECT
+ BY LEVEL<13; -- Oracle (10g)
SELECT TO_CHAR(TO_DATE(LEVEL,'MM')-1,'DD')FROM Dual CONNECT BY LEVEL<1
+3;
select to_char(to_date(generate_series(2,13)::text,'MM')+interval'1y -
+1s','DD'); -- Pg. awful but works :)
SELECT TO_CHAR(LAST_DAY(TO_DATE(LEVEL,'MM')),'DD')FROM Dual CONNECT BY
+ LEVEL<13; -- Oracle. "saved 3 char'
SELECT TO_CHAR(TO_DATE(LEVEL,'MM')-1,'DD')FROM Dual CONNECT BY LEVEL<1
+3; -- Oracle. "start from december" (shortest)
select 30+(3+(3*m+3)%5-(3*m+1)%5)/5from generate_series(3,13)f(m); --
+ wrog's (short but incomplete: march through january)
Looks like Oracle wins this little golfing contest. Ah well... it has to have something to justify the price ;-))
Well played chacham++ :-)
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.