Re: How do you remember the number of days in each month?

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)
[download]

Or, golfed down a bit...

select to_char(generate_series('20140201','20150101',interval'1mon')-i
+nterval'1','DD');
[download]

...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)
[download]

Looks like Oracle wins this little golfing contest. Ah well... it has to have something to justify the price ;-))

Well played chacham++ :-)

Comment on Re: How do you remember the number of days in each month? Select or Download Code

Replies are listed 'Best First'.

Re^2: How do you remember the number of days in each month?
by chacham (Prior) on Sep 16, 2014 at 16:08 UTC

About the same in Oracle

SQL> SELECT
  2     LEVEL Month,
  3     TO_CHAR(LAST_DAY(TO_DATE(LEVEL  || '/1/' ||
  4     TO_CHAR(SYSDATE, 'YYYY'), 'MM/DD/YYYY')),'DD') Days
  5  FROM
  6     Dual
  7  CONNECT BY
  8     LEVEL <= 12;

     MONTH DA
---------- --
         1 31
         2 28
         3 31
         4 30
         5 31
         6 30
         7 31
         8 31
         9 30
        10 31
        11 30
        12 31

12 rows selected.
[download]

After the update in the parent, i realized this too could be shortened:

SELECT
    TO_CHAR(ADD_MONTHS(TO_DATE('1', 'DDD'), LEVEL) - 1, 'DD')
FROM
    Dual
CONNECT BY
    LEVEL < 13;
[download]

cheat

SELECT
    TO_CHAR(ADD_MONTHS(DATE '2014-1-1', LEVEL) - 1, 'DD')
FROM
    Dual
CONNECT BY
    LEVEL < 13;
[download]

In a race with the parent to find the shortest SQL. :) So far we went with a literal data (which locks it to 2014), but he outdid me by removing the '20' from the date. Touché!

Though, not to be outdone, we'll find something shorter (as of update 4) and even go back to working for every year:

SELECT TO_CHAR(LAST_DAY(TO_DATE(LEVEL,'MM')),'DD')FROM Dual CONNECT BY
+ LEVEL<13;
[download]

Saved 3 characters. :)

If we can start from December, we'll save 8 more characters (for a total of 11):

SELECT TO_CHAR(TO_DATE(LEVEL,'MM')-1,'DD')FROM Dual CONNECT BY LEVEL<13;

[reply]
[d/l]
[select]


Think about Loose Coupling
	PerlMonks