selecting dates till current date - mysql

Question

Today is 20th Aug 2013. I want to generate 20 rows which will contain dates from 1st to 20th (whatever would be the current date) of the month by using mysql query. Count should always start from 1st date of the month and till current date... output would be like, only one column and multiple rows till current date like given below..

    Current month
    8/1/13 12:00 AM
    8/2/13 12:00 AM
    8/3/13 12:00 AM
    8/4/13 12:00 AM
    8/5/13 12:00 AM
    8/6/13 12:00 AM
    8/7/13 12:00 AM
    8/8/13 12:00 AM
    8/9/13 12:00 AM
    8/10/13 12:00 AM
    8/11/13 12:00 AM
    8/12/13 12:00 AM
    8/13/13 12:00 AM
    8/14/13 12:00 AM
    8/15/13 12:00 AM
    8/16/13 12:00 AM
    8/17/13 12:00 AM
    8/18/13 12:00 AM
    8/19/13 12:00 AM
    8/20/13 12:00 AM

I tried following query but is of no use. Can you please help to find some other workaround for this?

DECLARE @startDate DATETIME=CAST(MONTH(GETDATE()) AS VARCHAR) + '/' + '01/' +  + CAST(YEAR(GETDATE()) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATETIME= GETDATE() -- mm/dd/yyyy

;WITH Calender AS 
(
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT CalanderDate + 1 FROM Calender
    WHERE CalanderDate + 1 <= @endDate
)
SELECT [Date] = CONVERT(VARCHAR(10),CalanderDate,25) 
FROM Calender
OPTION (MAXRECURSION 0)

Why are you using MySQL for this? – Strawberry Aug 20 '13 at 11:41 — Strawberry, Aug 20 '13 at 11:41

score 2 · Accepted Answer · answered Aug 20 '13 at 11:56

This too would work by dynamically building a result set of all days, but can work against any existing table you have that has as least 31 days (max for any given month).

select
      @curDay := date_add( @curDay, interval 1 day ) as CalendarDay
   from
      ( select @curDay := date_add( DATE_FORMAT(NOW(),
         '%Y-%m-01'), interval -1 day) ) sqlvars,
      AnyTableInYourDatabaseWithAtLeast31Records
   where
      @curDay <= now()
   limit
      31

The first part of select @curDay builds whatever the current day is, gets to the first of the month, then subtracts 1 day from it giving you the last day of the previous month. Then, the outer select @curDay := keeps updating itself by adding 1 day as the CalendarDay result column. Since its a join to "any table" in your database, it will keep grabbing a MAX of 31 records, but only return where the date is less than or current to now.

score 0 · Answer 2 · answered Aug 20 '13 at 11:46

Fiddle at

http://www.sqlfiddle.com/#!2/28466/1

CREATE TABLE CALENDAR(DATE1 DATETIME);

INSERT INTO CALENDAR VALUES ('2013/8/1 12:00:00');
INSERT INTO CALENDAR VALUES ('2013/8/2 12:00:00');
INSERT INTO CALENDAR VALUES ('2013/8/3 12:00:00');
INSERT INTO CALENDAR VALUES ('2013/8/4 12:00:00');....

SELECT DISTINCT DATE1 FROM Calender where MONTH(DATE1)=MONTH(NOW()) and DAYOFMONTH(DATE1) <=DAYOFMONTH(NOW())

This gives the output

score 0 · Answer 3 · answered Aug 20 '13 at 11:55

I like to use tally tables for these sorts of problems, they tend to be pretty fast:

DECLARE @startDate DATETIME= CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101);
WITH 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 AS t1 CROSS JOIN N0 AS t2)
,N2 as (SELECT 1 as n FROM N1 AS t1 CROSS JOIN N1 AS t2)
,N3 as (SELECT 1 as n FROM N2 AS t1 CROSS JOIN N2 AS t2)
,N4 as (SELECT 1 as n FROM N3 AS t1 CROSS JOIN N3 AS t2)
,N5 as (SELECT 1 as n FROM N4 AS t1 CROSS JOIN N4 AS t2)
,N6 as (SELECT 1 as n FROM N5 AS t1 CROSS JOIN N5 AS t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
SELECT DATEADD(day,num-1,@startDate) as theDate
FROM nums
WHERE num <= DATEDIFF(day,@startDate,GETDATE()) + 1

score 0 · Answer 4 · answered Aug 20 '13 at 12:06

You could try calling this Stored Procedure;

    DELIMITER $$

    CREATE PROCEDURE `test`.`GenerateDates` ()

BEGIN
    DECLARE Days INTEGER;
    DECLARE Count INTEGER;  

    SET Days = DATEDIFF(NOW(),CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-01'));
    SET Count = 0;

    DROP TEMPORARY TABLE IF EXISTS tempDates;
    CREATE TEMPORARY TABLE tempDates
        (
            YourDate Date,
            PRIMARY KEY(YourDate)
        );


    WHILE (Count <= Days) DO
       INSERT INTO tempDates (YourDate) VALUES
       (DATE_FORMAT(DATE_ADD(CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-01'), INTERVAL Count DAY),'%Y-%m-%d'));
       SET Count = Count + 1;
    END WHILE;

    SELECT * FROM tempDates;
END

score 0 · Answer 5 · answered Aug 20 '13 at 12:11

:) ... or if a table of 31 integers seems like a stretch, how about a table of 10...

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT DATE_FORMAT(CURDATE(),'%Y-%m-01')+INTERVAL i2.i*10+i1.i DAY x FROM ints i1, ints i2 HAVING x <= NOW();
+------------+
| x          |
+------------+
| 2013-08-01 |
| 2013-08-02 |
| 2013-08-03 |
| 2013-08-04 |
| 2013-08-05 |
| 2013-08-06 |
| 2013-08-07 |
| 2013-08-08 |
| 2013-08-09 |
| 2013-08-10 |
| 2013-08-11 |
| 2013-08-12 |
| 2013-08-13 |
| 2013-08-14 |
| 2013-08-15 |
| 2013-08-16 |
| 2013-08-17 |
| 2013-08-18 |
| 2013-08-19 |
| 2013-08-20 |
+------------+

(still not sure why you'd do this in MySQL)

selecting dates till current date - mysql

5 Answers5