search

fjtello / SQL-C-sharp

Common programming and coding tips and howtos
0 stars 0 forks source link

SQL hints [crear tabla de fechas] #14

Open fjtello opened 7 years ago

fjtello commented 7 years ago

-- Crear tabla de fechas BEGIN DECLARE @diasPorDefecto as int; SET @diasPorDefecto = 365; DECLARE @fechaInicial AS DATETIME; DECLARE @fechaFinal AS DATETIME; 

SET @fechaInicial = DATEADD(day, @diasPorDefecto * -1, GETDATE()); 
SET @fechaFinal = GETDATE();

DECLARE @f AS TABLE (id INT IDENTITY(1,1), Fecha DATETIME);

DECLARE @fini AS DATETIME; SET @fini = @fechaInicial; 
DECLARE @ffin AS DATETIME; SET @ffin = @fechaFinal;

IF(@fini >= @ffin) SET @fini = DATEADD(d, -1, @ffin);

WHILE (@fini <= @ffin)
    BEGIN
        INSERT INTO @f VALUES (@fini);
        SET @fini = DATEADD(d, 1, @fini);
    END

UPDATE @f SET Fecha = CONVERT(DATETIME, CONVERT(VARCHAR(10), Fecha, 111));

END