0

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.

Edited by Ichcha: n/a

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by adam_k
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.