Insert all dates from a month into a table

Question

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?

Answer 1

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.

Answer 2

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

https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server

I hope it helps