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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy