Hi,
If you want to get every month 10th date for one year use this.
Declare @FromDate VARCHAR(20) ='2013-01-01'
Declare @ToDate VARCHAR(20) = '2013-12-12'
IF OBJECT_ID('tempdb..#TEMP_EveryWk_Snday') IS NOT NULL
DROP TABLE #TEMP_EveryWk_Snday
DECLARE @TOTALCount INT
SET @FromDate = DATEADD(DAY,-1,@FromDate)
Select @TOTALCount= DATEDIFF(dd,@FromDate,@ToDate);
WITH d AS
(
SELECT top (@TOTALCount) AllDays = DATEADD(DAY, ROW_NUMBER()
OVER (ORDER BY object_id), REPLACE(@FromDate,'-',''))
FROM sys.all_objects
)
Select Distinct
DATEADD(MONTH, DATEDIFF(MONTH,number, d.AllDays), 09)
FROM master..spt_values M ,d
WHERE M.Type = 'P' and M.number between 1 and 12
AND
AllDays >=@FromDate
AND AllDays <=@ToDate
group by
M.number
,d.AllDays