the following gets first date of the week
DECLARE @Year SMALLINT = 2011
;WITH cteSource(firstdate)
AS (
SELECT CONVERT(varchar(50), DATEADD(DAY, 7 * v.Number, o.Origin), 101) AS firstdate
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '18991230', STR(@Year, 4, 0) + '0101') / 7 * 7, '18991230')
) AS o(origin)
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND 52
)
SELECT firstdate
FROM cteSource
WHERE DATEPART(YEAR, firstdate) = @Year
while the following gets last date
DECLARE @Year SMALLINT = 2011
;WITH cteSource(lastdate)
AS (
SELECT CONVERT(varchar(50), DATEADD(DAY, ((7 * v.Number)-1), o.Origin), 101) AS lastdate
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '18991223', STR(@Year, 4, 0) + '0101') / 7 * 7, '18991223')
) AS o(origin)
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND 52
)
SELECT lastdate
FROM cteSource
WHERE DATEPART(YEAR, lastdate) = @Year
i wanted to join the query to get the output in 1 table with 2 columns. while i tried using
select fistdate,lastdate from(query1)a,(query2)b
am getting the following output :
firstdate | lastdate
01/01/2011 | 01/07/2011
01/08/2011 | 01/07/2011
01/15/2011 | 01/07/2011
. | .
. | .
01/01/2011 | 01/14/2011
01/08/2011 | 01/14/2011
but my expected output is :
firstdate | lastdate
01/01/2011 | 01/07/2011
01/08/2011 | 01/14/2011
01/15/2011 | 01/21/2011
please help.. thanks in advance.