David Gerler
asked on
Using T-SQL Need to return first day of the 34th week last year.
I need to calculate what the date was for the first day of the 34th week of last year. This so I can compare week 34 last year to this week (also week 34).
I am using Sql Server 2005.
I am using Sql Server 2005.
SELECT DATEADD(week,34,'2009-01-0 1')
declare @d datetime
declare @numbers table (n int)
set @d = '5/13/2005'
insert into @numbers(n)
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select -1 union all
select -2 union all
select -3 union all
select -4 union all
select -5 union all
select -6
select min(d) AS WeekBegin, max(d) AS WeekEnd
from
(
select dateadd(d, n, @d) as d, datepart(week, dateadd(d, n, @d)) as w
from @numbers
) t
where datepart(week, @d) = w
You might want to consider making the numbers table a permanent table so you don't have to build it each time.
EDIT: modified the numbers table for the values that we need.
ASKER
My week should always start on Monday and week one would be the first full week of the year. In other words, week one starts with the first Monday of the year.
DECLARE @YearBegin DATE = (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
DECLARE @WKBegin DATE = (SELECT DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day, @YearBegin ),@YearBeg in)), 0))
SELECT DATEADD(week,34,@WKBegin)
DECLARE @WKBegin DATE = (SELECT DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,
SELECT DATEADD(week,34,@WKBegin)
You may want to redefine what is current week, According to your week numbering this week is not 34 week of the year, but 33
declare @lastYearFM datetime, @thisYearFM datetime, @oneOff int, @thisWeek int
select @thisYearFM=getdate() - datepart(dy,getdate())+1
select @lastYearFM= dateadd(year,-1,@thisYearF M), @oneOff = 0
select @thisYearFM, @lastYearFM
if datepart(dw,@lastYearFM) > 2 select @lastYearFM = @lastYearFM - datepart(dw,@lastYearFM), @oneOff=1
select @thisWeek = datepart(week,getdate())
select @thisYearFM, @lastYearFM, @thisWeek
select @lastYearFM+7*@thisWeek
declare @lastYearFM datetime, @thisYearFM datetime, @oneOff int, @thisWeek int
select @thisYearFM=getdate() - datepart(dy,getdate())+1
select @lastYearFM= dateadd(year,-1,@thisYearF
select @thisYearFM, @lastYearFM
if datepart(dw,@lastYearFM) > 2 select @lastYearFM = @lastYearFM - datepart(dw,@lastYearFM), @oneOff=1
select @thisWeek = datepart(week,getdate())
select @thisYearFM, @lastYearFM, @thisWeek
select @lastYearFM+7*@thisWeek
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Could be. I got the week number from what vbscript gave me and it's probably using "System default"
What I really need is a script that will return a Monday at the beginning of a numbered week. this year and last.
The ultimate goal is to be able to compare this week to date to last year's week to date for the corresponding week.
Week 1 of a given year will be the first full week of the year. This allows us to compare Monday and Tuesday of this week to a Monday and Tuesday last year in the corresponding week whether this week is 33 or 34.
What I really need is a script that will return a Monday at the beginning of a numbered week. this year and last.
The ultimate goal is to be able to compare this week to date to last year's week to date for the corresponding week.
Week 1 of a given year will be the first full week of the year. This allows us to compare Monday and Tuesday of this week to a Monday and Tuesday last year in the corresponding week whether this week is 33 or 34.
You can get the first Monday of any year with the computation shown below.
To get a specific week-starting date, we just need to add (wk# - 1) * 7 days to that starting date.
For example:
To get a specific week-starting date, we just need to add (wk# - 1) * 7 days to that starting date.
For example:
DECLARE @date datetime
DECLARE @date2 datetime
DECLARE @weekno tinyint
SET @date = DATEADD(YEAR, -0, GETDATE()) --adjust -0 to test for different years
SET @date2 = DATEADD(YEAR, -1, GETDATE()) --get prior yr of @date year
SET @weekno = 34
SELECT DATEADD(DAY, (@weekno - 1) * 7,
--determine first Monday of the year
DATEADD(WEEK, (DATEDIFF(DAY, 0, CAST(YEAR(@date) AS char(4)) + '0101') + 6) / 7, 0)) AS WeekNoMondayDate,
DATEADD(DAY, (@weekno - 1) * 7,
--determine first Monday of the year
DATEADD(WEEK, (DATEDIFF(DAY, 0, CAST(YEAR(@date2) AS char(4)) + '0101') + 6) / 7, 0)) AS WeekNoMondayDate2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lee
Open in new window