July 27, 2016 at 5:38 am
I have week codes like 201551 , 201552
I want the sql query to return firstday and last day for those weeks
July 27, 2016 at 6:22 am
sanjayasamantaray (7/27/2016)
I have week codes like 201551 , 201552I want the sql query to return firstday and last day for those weeks
How are the weeks defined?
Is the first day Saturday, Sunday or Monday?
See this article for more information.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2016 at 7:10 am
maybe a start ?
SET DATEFIRST 1 -- WEEK IS MONDAY THRO SUNDAY
DECLARE @input INT= 201552;
DECLARE @wk INT= RIGHT(@input, 2);
DECLARE @yr INT= LEFT(@input, 4);
DECLARE @fdow DATE =
(SELECT CONVERT( DATE, DATEADD(week, @wk, DATEADD(year, @yr - 1900, 0))
- 4 -
DATEPART(dw, DATEADD(week, @wk, DATEADD(year, @yr - 1900, 0)) - 4) + 1, 102));
DECLARE @ldow DATE = DATEADD(DAY,6,@fdow);
PRINT @fdow
PRINT @ldow
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 27, 2016 at 7:28 am
Or you could use a table with all possible values.
Here's an example on how to create it.
CREATE TABLE Weeks(
WeekCode int,
FisrtDay date,
LastDay date
);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP 3000 DATEADD( wk, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1, '19950102') n
FROM E4
)
INSERT INTO Weeks
SELECT (YEAR(n) * 100) + DATEPART(ISOWK, n) WeekCode,
n AS firstday,
n + 7 AS lastday
FROM cteTally;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply