RE:generar todos los dias del mes
Publicado por
Diego (1 intervención) el 05/10/2018 19:02:26
Yo arme esta vista y genera todos los dias de un año, en este caso el año 2018, a partir de aca se puede hacer todo lo que te imagines con fechas.-
CODIGO:
create view vista_que_muestra_fechas_corridas_organizadas_por_meses as
Select dt_val from (
-- enero
select (dateadd(dd, value, dateadd(month,
1 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
1 --***** Modificar el mes
union
Select dt_val from (
-- febrero
select (dateadd(dd, value, dateadd(month,
2 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
2 --***** Modificar el mes
union
Select dt_val from (
-- marzo
select (dateadd(dd, value, dateadd(month,
3 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
3 --***** Modificar el mes
union
Select dt_val from (
-- abril
select (dateadd(dd, value, dateadd(month,
4 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
4 --***** Modificar el mes
union
Select dt_val from (
-- mayo
select (dateadd(dd, value, dateadd(month,
5 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
5 --***** Modificar el mes
union
Select dt_val from (
-- junio
select (dateadd(dd, value, dateadd(month,
6 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
6 --***** Modificar el mes
union
Select dt_val from (
-- julio
select (dateadd(dd, value, dateadd(month,
7 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
7 --***** Modificar el mes
union
Select dt_val from (
-- agosto
select (dateadd(dd, value, dateadd(month,
8 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
8 --***** Modificar el mes
union
Select dt_val from (
-- septiembre
select (dateadd(dd, value, dateadd(month,
9 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
9 --***** Modificar el mes
union
Select dt_val from (
-- octubre
select (dateadd(dd, value, dateadd(month,
10 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
10 --***** Modificar el mes
union
Select dt_val from (
-- noviembre
select (dateadd(dd, value, dateadd(month,
11 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
11 --***** Modificar el mes
union
Select dt_val from (
-- diciembre
select (dateadd(dd, value, dateadd(month,
12 - --***** Modificar el mes
1, dateadd(year,
2018 --***** Modificar el AÑO
- 1900, '1900.01.01')))) as dt_val from( select (v2 * 4 + v1) * 4 + v0 as value from (select 0 as v0 union select 1 union select 2 union select 3) as rs0 cross join (select 0 as v1 union select 1 union select 2 union select 3) as rs1 cross join (select 0 as v2 union select 1 union select 2 union select 3) as rs2 ) as rs ) as rs2 where
month(dt_val) =
12 --***** Modificar el mes
union .... etcetera . aca repetis todo nuevamente haciendo tanta cantidad de años como quieras, el año lo "fabricas" en la fila que dice:
2018 --***** Modificar el AÑO