1

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?

3
  • 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

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.

0

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

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

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .