enumerate days of week in t-sql

Is anybody to know how to enumerate all week's days in T-SQL, so that we have in the开发者_如何学JAVA ouput a string like: 'Sunday, Monday, Tuesday, ...'

---

WITH    week (dn) AS
        (
        SELECT  1
        UNION ALL
        SELECT  dn + 1
        FROM    week
        WHERE   dn < 7
        )
SELECT  DATENAME(dw, dn + 5) 
FROM    week

Replace dn + 5 with dn + 6 if your week starts from Monday.

If you need a single comma separated string instead of a set, use this:

WITH    week (dn, dname) AS
        (
        SELECT  1, CAST(DATENAME(dw, 6) AS NVARCHAR(MAX))
        UNION ALL
        SELECT  dn + 1, dname + ', ' + DATENAME(dw, dn + 6)
        FROM    week
        WHERE   dn < 7
        )
SELECT  dname
FROM    week
WHERE   dn = 7

---

A straight select that will work with any SET DATEFIRST setting

select
    datename(dw,   6-@@datefirst) + ', ' +
    datename(dw, 1+6-@@datefirst) + ', ' +
    datename(dw, 2+6-@@datefirst) + ', ' +
    datename(dw, 3+6-@@datefirst) + ', ' +
    datename(dw, 4+6-@@datefirst) + ', ' +
    datename(dw, 5+6-@@datefirst) + ', ' +
    datename(dw, 6+6-@@datefirst)

If you don't care about region (Monday or Sunday as first day of week), then just

select
    datename(dw, 0) + ', ' + datename(dw, 1) + ', ' +
    datename(dw, 2) + ', ' + datename(dw, 3) + ', ' +
    datename(dw, 4) + ', ' + datename(dw, 5) + ', ' +
    datename(dw, 6) + ', '

It will perform much better than going through CTE and will also work in 2000 should you ever need it.

---

The fastest means is to statically define the comma separated list. I don't know if SET DATEFIRST affects only the database -- if it's the entire instance, I would really hesitate to use SET DATEFIRST.

Rather than using recursion, you can use the values from MASTER..SPT_VALUES, and a combination of the STUFF and FOR XML PATH functions (Caveat: SQL Server 2005+):

Week starting on Sunday:

SELECT STUFF((SELECT ', ' + x.wkday_name 
                FROM (SELECT DISTINCT DATENAME(dw, t.number) AS wkday_name, 
                             t.number
                        FROM MASTER.dbo.SPT_VALUES t
                       WHERE t.number BETWEEN -1 AND 5) x
                    ORDER BY x.number
                     FOR XML PATH ('')), 1, 2, '')

Week starting on Monday:

SELECT STUFF((SELECT ', ' + x.wkday_name 
                FROM (SELECT DISTINCT DATENAME(dw, t.number) AS wkday_name, 
                             t.number
                        FROM MASTER.dbo.SPT_VALUES t
                       WHERE t.number BETWEEN 0 AND 6) x
                    ORDER BY x.number
                     FOR XML PATH ('')), 1, 2, '')

Comparison:

The statically defined list won't return a query plan for me on SQL Server 2005. Quassnoi's recursion example on 2005 has a subtree cost of 0.0000072; SPT_VALUES has a subtree cost of 0.0158108. So the recursive approach is appears more efficient than SPT_VALUES -- possibly due to the very small size?