January 22, 2010 at 8:40 am
I have a report that uses datepart to get the week numbers for a year based on user parameter. It has 52 columns, one for each month. The header displays WK1, WK2, WK3, etc... Now the user wants to see the actual date range for each week in the header, for example, for WK1 of 2009, it would be 1/1-1/3 (no need to show year), for WK2 2009, it would be 1/4-1/10 (Sat-Sun). Is this possible?
January 22, 2010 at 10:10 am
What would be Week one for this year?
Either from 01-02 (Fr - Sat) or 03-09 (Sun-Sat)?
Or, to phrase it a little different:
What needs to be the first day of the week and what will determine week #1 (per ISO or per SQL Server standard setting?)
January 22, 2010 at 10:48 am
lmu92 (1/22/2010)
What would be Week one for this year?Either from 01-02 (Fr - Sat) or 03-09 (Sun-Sat)?
Or, to phrase it a little different:
What needs to be the first day of the week and what will determine week #1 (per ISO or per SQL Server standard setting?)
good question. week 1 for 2010 would be 01-02 which is SQL Server standard i assume
January 22, 2010 at 11:06 am
Amongst a list of T_SQL for date manipulation, I am sure you will find one that meets your objective in this blog posting by Lynn Pettis
https://qa.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx
January 22, 2010 at 11:44 am
Maybe something like this?
Note 1: I used the tally table as described in the related link in my signature to generate the weeks
Note 2: You may notice that this year actually has 53 weeks... You need to deal with it in your 52 weeks report...
DECLARE @yr CHAR(4),
@day1 DATETIME
SET @yr='2010'
SET @day1=DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,@yr+'0101'),0))
;WITH cte AS
(
SELECT n-1 AS n
FROM tally
WHERE n<54
)
SELECT
n+1 as wk,
CASE
WHEN DATEPART(YEAR,DATEADD(wk,n,@day1))<@yr
THEN '01/01'
ELSE CONVERT(CHAR(5),(DATEADD(wk,n,@day1)),101)
END
+ '-'
+
CASE
WHEN DATEPART(YEAR,DATEADD(dd,6,DATEADD(wk,n,@day1)))>@yr
THEN '12/31'
ELSE CONVERT(CHAR(5),(DATEADD(dd,6,DATEADD(wk,n,@day1))),101)
END
FROM cte
January 22, 2010 at 12:15 pm
lmu92 (1/22/2010)
Maybe something like this?Note 1: I used the tally table as described in the related link in my signature to generate the weeks
Note 2: You may notice that this year actually has 53 weeks... You need to deal with it in your 52 weeks report...
DECLARE @yr CHAR(4),@day1 DATETIME
SET @yr='2010'
SET @day1=DATEADD(dd,-1,DATEADD(wk,DATEDIFF(wk,0,@yr+'0101'),0))
;WITH cte AS
(
SELECT n-1 AS n
FROM tally
WHERE n<54
)
SELECT
n+1 as wk,
CASE
WHEN DATEPART(YEAR,DATEADD(wk,n,@day1))<@yr
THEN '01/01'
ELSE CONVERT(CHAR(5),(DATEADD(wk,n,@day1)),101)
END
+ '-'
+
CASE
WHEN DATEPART(YEAR,DATEADD(dd,6,DATEADD(wk,n,@day1)))>@yr
THEN '12/31'
ELSE CONVERT(CHAR(5),(DATEADD(dd,6,DATEADD(wk,n,@day1))),101)
END
FROM cte
awesome!
January 22, 2010 at 12:42 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply