I'm confused. How to get all dates from a month and insert them into a table in a database?

I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table attendance it will generate one row for each date.

I have table period

period | start_period | end_period
April  |   2016-04-01 | 2016-04-30

and I have table attendance

attendance_date  | check_in | check out
2016-04-01       |     null |      null
2016-04-30       |     null |      null

How to get all dates between 01-04-2016 and 30-04-2016 and insert them into table attendance?

  • 4
    Can you connect Microsoft SQL Server with MySQL workbench?
    – Vérace
    Apr 25, 2016 at 8:36
  • 1
    Possible duplicate of How to create a range of dates Apr 25, 2016 at 9:58
  • Do you want an insert trigger on period to insert rows into attendance?
    – Andriy M
    Apr 26, 2016 at 7:17

2 Answers 2


You can use variables to mimic a recursive CTE:

SELECT @d:=(@d + interval 1 day) 
FROM (SELECT @d:=date('2016-03-31')) r
CROSS JOIN information_schema.tables t 
WHERE @d < '2016-04-30';

You need to make sure that you have a table to join against that have a sufficient number of rows for your need.

If you do this on a regular basis you might want to generate a calendar table that you can use to join against.

I heard rumors that recursive CTE:s as well as window functions will be available in a nearby future for MySQL. I have not investigated it very closely though, so it might just be rumours.


Try using following query

DECLARE @MinDate DATE = '20160401',
        @MaxDate DATE = '20160430';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

Please refer following link for more details


I hope it helps

  • still don't understand
    – Hardian Z
    Apr 25, 2016 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.