August 30, 2013 at 6:59 pm
declare @year int = 2013,
@week int = 27
declare @dte date
select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))
SELECT *
FROM
(
SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6)
) num (n)
) d
WHERE [DATE] >= dateadd(year, @year - 1900, 0)
AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)
my output needs to be as follows
[2013-06-30],[2013-07-01],[2013-07-02],[2013-07-03],[2013-07-04],[2013-07-05],[2013-07-06]
note : i need the symbol [ ] as well.
can any one help me in this
August 31, 2013 at 4:56 am
This is when you use FOR XML PATH(''). A completely obscure syntax for the purpose, but it works.
declare @year int = 2013,
@week int = 27
declare @dte date
select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))
; WITH dates AS (
SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
FROM (VALUES (0), (1), (2), (3), (4), (5), (6) ) num (n)
), commalist (datelist) AS (
SELECT (SELECT quotename(convert(date, DATE)) + ','
FROM dates
WHERE [DATE] >= dateadd(year, @year - 1900, 0)
AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)
ORDER BY DATE
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
)
SELECT substring(datelist, 1, len(datelist) - 1)
FROM commalist
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 31, 2013 at 7:13 am
thanks a lot gentle man.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply