Phil Parkin (2/6/2013)
Jason-299789 (2/6/2013)
(reposted from other thread)Your best bet is to use a TALLY table, a CTE, or at worst a Recursive CTE.
using an crude tally table
With Cte_n
AS
(
Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
UNION ALL Select 1 a Union ALL Select 1 Union ALL Select 1 Union ALL Select 1 Union ALL Select 1
),Cte_n1
AS
(
Select n1.a a from CTE_n n, Cte_n n1,Cte_n n2
),
Cte_Tally
AS
(
Select Row_Number() OVER (ORDER BY a) a from Cte_n1
)
Select
DateAdd(d,a-1,'01-Jan-1900')
From Cte_Tally
Working out the date parts year, months etc should be relatively simple.
Not the best bet IMO - doesn't even meet the requirements (what about the other columns?). Calendar table gets my vote.
Phil,
It was aimed as more of an example of how to generate the basic data (ie a Sequential date), so that you can then start adding on the additional attributes (month, year month, etc), my last comment about working out the date parts etc, was aimed at getting the OP to figure out the DATEPART, YEAR,MONTH, DAY, etc to add into the table, not that you dont need them in the table, though in review it does come across as the later.
The link to the SSC Article that Anthony posted covered the date table in more detail but I missed it before I re-posted the solution from the other thread.
_________________________________________________________________________
SSC Guide to Posting and Best Practices