Obtener fechas de sql para hacer un calendario en un gridview

[Beakdan]

Otro problema clásico que se resuelve con una tabla de números.
Si tienes SQL 2005 o 2008, puedes generar los valores al vuelo.

Código:

WITH z AS (SELECT 1 AS N UNION ALL SELECT 1),
y AS (SELECT a.N FROM z AS a CROSS JOIN z AS b),
x AS (SELECT a.N FROM y AS a CROSS JOIN y AS b),
Numbers AS (
    SELECT    ROW_NUMBER() OVER(ORDER BY a.N) AS N
    FROM    x AS a CROSS JOIN z
)
SELECT    DATEADD(day, N, DATEADD(day, -DAY(GETDATE()), CAST(CAST(CAST(GETDATE() AS FLOAT) AS INT) AS DATETIME)))
FROM    Numbers
WHERE    N <= DAY(GETDATE())

Si tienes SQL Server 2000, usa una tabla de números (tally table).


Saludos.