Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-08-18
10
Medium Priority
?
606 Views
Last Modified: 2012-05-10
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
Comment
Question by:dgerler
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33467056
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

Open in new window

0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33467149
SELECT DATEADD(week,34,'2009-01-01')
0
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33467238


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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:dgerler
ID: 33468059
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

by:vdr1620
ID: 33468372
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),@YearBegin)), 0))

SELECT DATEADD(week,34,@WKBegin)
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33468438
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

by:Larissa T
Larissa T earned 800 total points
ID: 33468645
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

by:dgerler
ID: 33468795
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 70

Expert Comment

by:Scott Pletcher
ID: 33470562
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

Open in new window

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1200 total points
ID: 33474354

drop function dbo.weekXMonday
go
create function dbo.weekXMonday(@year int, @weekno int)
returns datetime as
begin
declare @d datetime
set @d=dateadd(yy, @year-1900, 0)
return (dateadd(wk,@weekno-1,@d)-((datepart(dw,@d-2)+@@datefirst)%7))
return @d
end
GO
select dbo.weekXMonday(2009,34)
select dbo.weekXMonday(2010,34)

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question