SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE example(
  "name" VARCHAR(64),
  "start" DATETIME,
  "end" DATETIME
);
INSERT INTO example VALUES
  ('Bob', '2014-10-30', '2014-11-02')
;
1 rows affected

SELECT * FROM example;


name
start
end


Bob
2014-10-30 00:00:00.000
2014-11-02 00:00:00.000


DECLARE @MIN_DATE DATE = (SELECT MIN("start") from example);
DECLARE @MAX_DATE DATE = (SELECT MAX("end")   from example);

SELECT @MIN_DATE, @MAX_DATE;


(No column name)
(No column name)


2014-10-30
2014-11-02


DECLARE @MINDATE DATE = (SELECT MIN("start") from example);
DECLARE @MAXDATE DATE = (SELECT MAX("end")   from example);

WITH dates AS (
    SELECT @MINDATE AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < @MAXDATE
)

SELECT
  "name",
  dates.date_axis AS "start",
  dates.date_axis AS "end"
FROM dates
  INNER JOIN example
    ON dates.date_axis BETWEEN example."start" AND example."end"
OPTION(MAXRECURSION 32767)
;


name
start
end


Bob
2014-10-30
2014-10-30

Bob
2014-10-31
2014-10-31

Bob
2014-11-01
2014-11-01

Bob
2014-11-02
2014-11-02


WITH dates AS (
    SELECT CAST('2014-01-01' AS DATE) AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < '2015-01-01'
)

SELECT TOP 10 *  /* TOP 10 is just so it doesn't overpower the output */
FROM dates
OPTION(MAXRECURSION 500)
;


date_axis


2014-01-01

2014-01-02

2014-01-03

2014-01-04

2014-01-05

2014-01-06

2014-01-07

2014-01-08

2014-01-09

2014-01-10


WITH dates AS (
    SELECT DATEADD(DAY, -365, CAST(GETDATE() AS DATE)) AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < CAST(GETDATE() AS DATE)
)

SELECT TOP 10 *  /* TOP 10 is just so it doesn't overpower the output */
FROM dates
OPTION(MAXRECURSION 500)
;


date_axis


2023-01-15

2023-01-16

2023-01-17

2023-01-18

2023-01-19

2023-01-20

2023-01-21

2023-01-22

2023-01-23

2023-01-24


INSERT INTO example VALUES
  ('Joe', '2015-01-30', '2015-02-03'),
  ('Anil', '2016-06-30', '2016-07-04')
;
2 rows affected

DECLARE @MINDATE DATE = (SELECT MIN("start") from example);
DECLARE @MAXDATE DATE = (SELECT MAX("end")   from example);

WITH dates AS (
    SELECT @MINDATE AS date_axis
  UNION ALL
    SELECT DATEADD(DAY, 1, date_axis)
    FROM dates
    WHERE date_axis < @MAXDATE
)

SELECT
  "name",
  dates.date_axis AS "start",
  dates.date_axis AS "end"
FROM dates
  INNER JOIN example
    ON dates.date_axis BETWEEN example."start" AND example."end"
ORDER BY
  "name",
  "start"
OPTION(MAXRECURSION 32767)
;


name
start
end


Anil
2016-06-30
2016-06-30

Anil
2016-07-01
2016-07-01

Anil
2016-07-02
2016-07-02

Anil
2016-07-03
2016-07-03

Anil
2016-07-04
2016-07-04

Bob
2014-10-30
2014-10-30

Bob
2014-10-31
2014-10-31

Bob
2014-11-01
2014-11-01

Bob
2014-11-02
2014-11-02

Joe
2015-01-30
2015-01-30

Joe
2015-01-31
2015-01-31

Joe
2015-02-01
2015-02-01

Joe
2015-02-02
2015-02-02

Joe
2015-02-03
2015-02-03

name	start	end
Bob	2014-10-30	2014-10-30
Bob	2014-10-31	2014-10-31
Bob	2014-11-01	2014-11-01
Bob	2014-11-02	2014-11-02

name	start	end
Anil	2016-06-30	2016-06-30
Anil	2016-07-01	2016-07-01
Anil	2016-07-02	2016-07-02
Anil	2016-07-03	2016-07-03
Anil	2016-07-04	2016-07-04
Bob	2014-10-30	2014-10-30
Bob	2014-10-31	2014-10-31
Bob	2014-11-01	2014-11-01
Bob	2014-11-02	2014-11-02
Joe	2015-01-30	2015-01-30
Joe	2015-01-31	2015-01-31
Joe	2015-02-01	2015-02-01
Joe	2015-02-02	2015-02-02
Joe	2015-02-03	2015-02-03