find out first and last date if given year & week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

find out first and last date if given year & week

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-07-19 : 03:40:08

Hey guys,

I have been given the year and week.

Select T0.Week,T0.Year From Weeks T0

how can I determine the start date and end date for each week? I want to be able to return them all on one line.

eg.
T0.Week,T0.Year,(StartDate for the week), (EndDate for the week)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-19 : 06:46:33

I would create a calendar table
Then it becomes
select top 1 FirstDayOfWeek, LastDayOfWeek
from Inp.dim_Calendar
where Rep_Year = @year
and rep_week = @week

You can just use the cte in a query if yoou wish - or just the week calculation.
The problem is that if yoou do the calculation when it's used someone will come along and get it wrong. Also if the business decide to change it you have to change all the places it is used.

CREATE TABLE dim_Calendar
(
[dim_Calendar_id] [int] NOT NULL,
[Rep_Date] [datetime] NOT NULL,
[FirstDayOfMonth] [datetime] NOT NULL,
[LastDayOfMonth] [datetime] NOT NULL,
[Rep_Year] [int] NOT NULL,
[Rep_Month] [int] NOT NULL,
[Rep_WeekOfYear] [int] NOT NULL,
FirstDayOfWeek datetime not null ,
LastDayOfWeek datetime not null ,
[Rep_DayOfWeek] [int] NOT NULL,
PRIMARY KEY CLUSTERED ([dim_Calendar_id])
)
GO

truncate table dim_Calendar -- delete
;with cte as
(
select dte = convert(datetime,'20080101')
union all
select dte = DATEADD(dd,1,dte) from cte where dte < '20201231'
)
insert dim_Calendar
(
dim_Calendar_id,
Rep_Date,
FirstDayOfMonth,
LastDayOfMonth,
Rep_Year,
Rep_Month,
Rep_WeekOfYear,
FirstDayOfWeek ,
LastDayOfWeek ,
Rep_DayOfWeek
)
select dim_Calendar_id = convert(int,convert(varchar(8),dte,112))
, Rep_Date = dte
, FirstDayOfMonth = DATEADD(mm,datediff(mm,0,dte),0)
, LastDayOfMonth = DATEADD(mm,datediff(mm,0,dte)+1,0)-1
, Rep_Year = YEAR(dte)
, Rep_Month = MONTH(dte)
, Rep_WeekOfYear = DATEPART(ww,dte)
, FirstDayOfWeek = DATEADD(wk,datediff(wk,0,dte),0)-1
, LastDayOfWeek = DATEADD(wk,datediff(wk,0,dte)+1,0)-2
, Rep_DayOfWeek = DATEPART(dw,dte)
from cte
option (maxrecursion 0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-19 : 07:20:32

SELECT
T0.WEEK,
T0.YEAR,
GETDATE() AS ACTUAL_DATE,
DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()) AS HOMOL_ACTUAL_DATE,
DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) AS HOMOL_WEEK_ACTUAL_DATE,
DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE())) AS DAY_IN_THE_WEEK,
DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) AS DAY_OF_THE_WEEK_OF_DAY_IN_THE_WEEK,
DATEADD(DD, -DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) + 1, DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) as FIRST_DAY_OF_THE_WEEK

FROM WEEKS T0

------------------------
PS - Sorry my bad english

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 09:51:03

quote:
Originally posted by pavlos

Hey guys,

I have been given the year and week.

Select T0.Week,T0.Year From Weeks T0

how can I determine the start date and end date for each week? I want to be able to return them all on one line.

eg.
T0.Week,T0.Year,(StartDate for the week), (EndDate for the week)

you need to use a logic like below given a year value and week value as integer parameters


declare @yr int= 2012,@wk int=5


select dateadd(wk,@wk-1,dateadd(yy,@yr-1900,0)) as startdate,dateadd(wk,@wk,dateadd(yy,@yr-1900,0)) -1 as enddate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-07-20 : 03:44:03

DATEADD(DD, -DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) + 1, DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) as FIRST_DAY_OF_THE_WEEK

brilliant!!

thanks jleitao

I just added 6 to get the end date, good piece of code!

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 05:11:09

you can use visakh16 query to get first day and last day

------------------------
PS - Sorry my bad english

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-20 : 06:13:59

Ok as long as you have the week start day on the server set consistently.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 10:11:17

quote:
Originally posted by nigelrivett

Ok as long as you have the week start day on the server set consistently.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yep...thats true

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources