-- Create Number Table
    IF EXISTS
    (
        SELECT 1
        FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N'dbo.TableOfNumbers')
            AND OBJECTPROPERTY(id, N'IsTable') = 1
    )
        DROP TABLE dbo.TableOfNumbers
GO

    CREATE TABLE dbo.TableOfNumbers
    (
        DummyNumber INT
    )
GO

-- Create work table populated with 0-9       
    IF EXISTS
    (
        SELECT 1
        FROM tempdb.dbo.sysobjects
        WHERE name LIKE '#num%'
    )
        DROP TABLE #num
GO
    SELECT
        '0' AS num
    INTO 
        #num
    UNION ALL 
    SELECT
        '1'
    UNION ALL
    SELECT
        '2'
    UNION ALL
    SELECT
        '3'
    UNION ALL
    SELECT
        '4'
    UNION ALL
    SELECT
        '5'
    UNION ALL
    SELECT
        '6'
    UNION ALL
    SELECT
        '7'
    UNION ALL
    SELECT
        '8'
    UNION ALL
    SELECT
        '9'

-- Use cross join to get 0-999 
-- join in further instances of temp table
-- to get more numbers if required
    INSERT INTO 
        dbo.TableOfNumbers
    SELECT 
        CAST(n1.num + n2.num + n3.num AS INT)
    FROM 
        #num AS n1 
        CROSS JOIN #num AS n2 
        CROSS JOIN #num AS n3 
    ORDER BY 
        n1.num, 
        n2.num, 
        n3.num

DECLARE @StartDate DATETIME
SET @StartDate = '1/Jan/2006'

SELECT
	DATEADD(day, ton.DummyNumber, @StartDate) AS FullDate, 
	DATENAME(weekday,DATEADD(day, ton.DummyNumber, @StartDate)) AS DayOfWeek  
FROM
	dbo.TableOfNumbers AS ton
WHERE
	ton.DummyNumber BETWEEN 0 AND 364

quote:

---

Originally posted by Sun Foster

I need a table in which store entire 2006 day and day of week. (365 rows) Is it possible to code to make?

---

declare
	@start_date	datetime

select	@start_date	= '2006-01-01'

select 	dateadd(day, NUMBER, @start_date) as dte,
	datepart(weekday, dateadd(day, NUMBER, @start_date)) as [day of week]
from 	dbo.F_TABLE_NUMBER_RANGE(0,365)
where	dateadd(day, NUMBER, @start_date) < dateadd(year, 1, @start_date)