Solved

# Using T-SQL Need to return first day of the 34th week last year.

Posted on 2010-08-18
541 Views
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.
0
Question by:dgerler
• 2
• 2
• 2
• +4

LVL 25

Expert Comment

Well, as the 34th week is 238 days in try this:

Lee
declare @dt datetime

select @dt = cast('1 Jan 2009' as datetime) + 238

select @dt
0

LVL 16

Expert Comment

0

LVL 10

Expert Comment

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.

0

Author Comment

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.
0

LVL 16

Expert Comment

DECLARE @YearBegin DATE = (SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

0

LVL 11

Expert Comment

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,@thisYearFM), @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
0

LVL 11

Assisted Solution

Larissa T earned 200 total points
Sorry,  +/- 1 errors.
I am in US - so week is Sunday (day 1) - Saturday (day 7)
declare @lastYearFM datetime, @thisYearFM datetime, @oneOff int, @thisWeek int
select @thisYearFM=getdate() - datepart(dy,getdate())+1
select  @lastYearFM= dateadd(year,-1,@thisYearFM), @oneOff = 0

select @thisYearFM, @lastYearFM, @thisWeek

select @thisYearFM,datepart(dw,@thisYearFM), @lastYearFM,datepart(dw,@lastYearFM)

if  datepart(dw,@lastYearFM) > 2       select @lastYearFM = @lastYearFM + (8-datepart(dw,@lastYearFM)), @oneOff=1
if  datepart(dw,@thisYearFM) > 2       select @thisYearFM = @thisYearFM + (8-datepart(dw,@thisYearFM))

select @thisWeek = datepart(week,getdate())-@oneOff

select @thisYearFM, @lastYearFM, @thisWeek

select @lastYearFM+7*(@thisWeek-1)
0

Author Comment

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.
0

LVL 69

Expert Comment

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:
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
0

LVL 58

Accepted Solution

cyberkiwi earned 300 total points

drop function dbo.weekXMonday

go

create function dbo.weekXMonday(@year int, @weekno int)

returns datetime as

begin

declare @d datetime

return @d

end

GO

select dbo.weekXMonday(2009,34)

select dbo.weekXMonday(2010,34)
0

## Featured Post

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed