# How to generate a set of dates from a range?

4

I try to calculate the days of a range of dates, I have something of this style, however I get the amount of the range, my goal is to get the number of the day corresponding to the month.

What it does

Goal

``````SELECT NOMBRE,RFC,CURP,
DATEDIFF(DAY, CAST(LEFT(FECHA,8) AS DATE),
CAST(RIGHT(FECHA,8) AS DATE))DIAS FROM TABLA
``````

The date is of this form '2018010120180131' for that reason I extract.

asked by Summer 19.10.2018 в 00:18
source

6

Ideally, as Gbianchi has already told you, these problems should be resolved in the view layer and not in the business layer, however, I understand that sometimes this is not possible.

It would be very helpful if you had a date table for each day, so a simple `LEFT JOIN` to it would solve your problem. As I understand that you do not have it, the alternative is to generate it dynamically. Here the proof of concept:

``````CREATE TABLE Ejemplo (
Nombre VARCHAR(255),
Fecha VARCHAR(16)
)

INSERT INTO Ejemplo (Nombre, Fecha) VALUES ('LAURA', '2018010120180131')

;WITH CTE AS (
SELECT 1 AS N UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0
)
SELECT E.*,
FROM Ejemplo E
CROSS JOIN (SELECT ROW_NUMBER() OVER(ORDER BY T1.N) AS RN
FROM CTE T1
CROSS JOIN CTE T2
CROSS JOIN CTE T3
) N
WHERE DATEADD(DAY, N.RN-1, CONVERT(DATETIME,LEFT(E.Fecha,8))) <= CONVERT(DATETIME,RIGHT(E.Fecha,8))

DROP TABLE Ejemplo
``````

### Explanation:

• First of all you have to generate a number sequence to generate each date, there are many techniques, in this example we use a% rec_ `COMMON TABLE EXPRESSION` so that by `CROSS JOINS` we can generate a% co_of% number of rows, in this case to be 10 rows for three recursions enables us to generate about 1000 rows, adding more `X` multiply the values, but obviously we should always work with a limit
• With `CROSS JOINs` we generate the numerator from 1 to 1000
• Then we simply apply it to the real table using another `ROW_NUMBER() OVER(ORDER BY T1.N)` and we generate each date simply adding the numerator to the initial date and verifying not to exceed the limit
• Important: This should work correctly from SQL 2008, if it were not the case, surely you have to stop using the CTE and use another similar technique