Dates table YTD
-
Hi All, Currently in my stored procedure I need to build a table with all dates starting 1st Jan to the date passed (YTD). Cuurently the way I do is, I start from first date of year and loop till @Datepassed, ignoring Sat and Sun.
SELECT @tdate = @YEAR_start WHILE @tdate <= @Datepassed BEGIN if DATEPART (dw, @tdate) not in (1,7) INSERT INTO #DATES (tdate) VALUES (@tdate) SELECT @tdate = dateadd (dd,1,@tdate) ENDForeg:- if dec 12 2003 is passed, looping becomes inefficient. Does any body know a better way. Thanks Ruchi -
Hi All, Currently in my stored procedure I need to build a table with all dates starting 1st Jan to the date passed (YTD). Cuurently the way I do is, I start from first date of year and loop till @Datepassed, ignoring Sat and Sun.
SELECT @tdate = @YEAR_start WHILE @tdate <= @Datepassed BEGIN if DATEPART (dw, @tdate) not in (1,7) INSERT INTO #DATES (tdate) VALUES (@tdate) SELECT @tdate = dateadd (dd,1,@tdate) ENDForeg:- if dec 12 2003 is passed, looping becomes inefficient. Does any body know a better way. Thanks RuchiWhy rebuild something that is static? If you don't specify many various years, build either a global temp table or add a new table with incremental dates, ignoring Saturday and Sunday. To get the dates, then, just use something like:
select Date into #DATES
from DateCache
where Date between @dateStart and @dateEndAnother way would be to use an extended stored proc. Programming something like this in C/C++ would be very efficient. What's questionable is how much more quickly you could build or fill a table.
Microsoft MVP, Visual C# My Articles