10

I simply need to return a list of all days within a month. I'm not accessing a specific table. So I need a sql select statement if given the month of February, return the following:

Day
----
2011-02-01
2011-02-02
2011-02-03
... etc, etc. 
2011-02-27
2011-02-28

This should be fairly simple, i would think, if I knew the sql select statement I need to make. The month should be selectable, I'll pass that in from a webpage select box which lets the user select the month and year. This is going to be the basis of a report. I already have a select statement that will take each of the days and give counts on records related to those dates, but I need this table as a basis of my reports.

stephenbayer
  • 12,373
  • 15
  • 63
  • 98
  • 3
    if you are not accessing a table then y use the sql at all ? – Val Feb 22 '11 at 17:34
  • Why not do this in your programming language - probably a lot simpler. – Knubo Feb 22 '11 at 17:36
  • Out of curiosity why does this need to be accomplished in sql? Seems an awkward request that is more easily accomplished in your scripting language of choice. – Mr Griever Feb 22 '11 at 17:37
  • 1
    I'm not sure it's exactly a duplicate question, but you should look at http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates – Jacob Mattison Feb 22 '11 at 17:38
  • it's going to be a select query I will join to from other tables to group data by those dates. I have a table with a bunch of products, one column is a delivery date another column is a pickup date, given any date I can tell how many of these products are out at clients sites. but I need those dates to group by. since there is no column with specific dates. ie, a product is delivered on 02/02/2011 and picked up on 02/11/2011, i need to know how many products are out on say 02/04/2011.. that aforemented item would be included in that count for that day. I have the SQL that does it per day. – stephenbayer Feb 22 '11 at 17:38
  • yeah, not exactly a duplicate, I'm trying to see if it helps me right now – stephenbayer Feb 22 '11 at 17:42

3 Answers3

18

I agree with the comments, that something like this shouldn't be done in the database, but technically its possible. If you give the start and end date, adding additional numbers to the subquery if necessary:

SELECT '2011-02-01' + INTERVAL a + b DAY dte
FROM
 (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
    UNION SELECT 8 UNION SELECT 9 ) d,
 (SELECT 0 b UNION SELECT 10 UNION SELECT 20 
    UNION SELECT 30 UNION SELECT 40) m
WHERE '2011-02-01' + INTERVAL a + b DAY  <  '2011-03-01'
ORDER BY a + b

Results:

"2011-02-01"
"2011-02-02"
"2011-02-03"
....
"2011-02-28"
The Scrum Meister
  • 29,681
  • 8
  • 66
  • 64
  • awesome, that's exactly what i need. I'll try to figure out a better way to do what I need to do for these reports tonight, but I have a client with me right now, that want's to see the result of this select statement – stephenbayer Feb 22 '11 at 18:05
  • 2
    it works great, i'll put in another question with more detail to see if anyone has a better way of doing this, but the report generator i use takes in a single SQL query string, and returns an html table, so I can't really loop through it for every day and make multiple queries in my scripting language. I have to contain everything in a single SQL query. – stephenbayer Feb 22 '11 at 18:12
  • But this print distinct values only, how can i print all values? – saravanakumar May 28 '15 at 09:40
1

-- This may be overkill, but you can make a procedure like this:

use dbname;

DELIMITER $$

DROP PROCEDURE IF EXISTS `days_of_month` $$

CREATE PROCEDURE `days_of_month`(iDate DATETIME) DETERMINISTIC

BEGIN

    DECLARE last_day,mm,yy,dd INT DEFAULT 0;
    SET dd = 1;
    SET mm = month(iDate);
    SET yy = year(iDate);

    set iDate = case when iDate is null then now() else iDate end;
    SET last_day = date_format(LAST_DAY(iDate),'%d');

    DROP TABLE IF EXISTS `days_of_month_tblTemp`;
    CREATE TEMPORARY TABLE days_of_month_tblTemp(tmpDate DATE);

    label1: LOOP
         insert into days_of_month_tblTemp(tmpDate) values (concat(yy,'-',mm,'-',dd));
        SET dd = dd + 1;
        IF dd < (last_day+1) THEN ITERATE label1; END IF;
         LEAVE label1;
      END LOOP label1;
      SELECT * from days_of_month_tblTemp;  
END $$

DELIMITER ;

-- run it like this: CALL days_of_month('2012-02-22');

dobrien
  • 41
  • 4
0

I have slightly augmented to give the all preceeding dates for the last month:

SELECT (SELECT Date(NOW() - INTERVAL 1 MONTH)) + INTERVAL a + b DAY dte FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) d, (SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40) m WHERE (SELECT Date(NOW() - INTERVAL 1 MONTH)) + INTERVAL a + b DAY < (select date(now())) ORDER BY a + b;

TheGoogleFU_guru
  • 1