asked on

How to get date from year+week

Hi!

Have this query:

Declare
@SQL varchar(2000),
@version_id varchar(50),
@MyCursor CURSOR,
@perioden varchar(4),
@moneden varchar(2),
@aaret1 varchar(4),
@aaret2 int,
@week1 varchar(2),
@week2 int,
@periodeteller int,
@FORECASTYEARWEEK int,
@DATAAREAID varchar(4)


SET @MyCursor = CURSOR FAST_FORWARD
FOR
Select version_id From dbo.Perioder_tmp_perioder

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @version_id,@FORECASTYEARWEEK,@DATAAREAID
                                           
WHILE @@FETCH_STATUS = 0              
BEGIN
    
    SET @aaret1 = SUBSTRING (@FORECASTYEARWEEK,1,4)
    SET @aaret2 = CAST(@aaret1 AS INT)

    SET @week1 = SUBSTRING (@FORECASTYEARWEEK,5,2)
    SET @week2 = CAST(@week1 AS INT)


    

    -- Insert record in dimension table
    INSERT Into TMP_Forcast_Hit_Ratio_Art_version
    (    
    [DATAAREAID], 
    [ITEMID],
    [version_id],
    [FORECASTYEARWEEK],
    [ITEMIDDAT]  
    )
    SELECT 
      @DATAAREAID,
      ART.ITEMID,
      @version_id,
      @FORECASTYEARWEEK,
      ART.ITEMID+@DATAAREAID
    FROM dbo.DIM_Artikkel as ART
    WHERE (year(ART.CREATEDDATETIME))=@aaret2 and (Month(ART.CREATEDDATETIME))<=@week2
      
    FETCH NEXT FROM @MyCursor
    INTO @version_id,@FORECASTYEARWEEK,@DATAAREAID 
END

Open in new window

I want to test on Week and not Month -> Month(ART.CREATEDDATETIME))

Like DATEPART(week,@date) ->
WHERE (year(ART.CREATEDDATETIME))=@aaret2 and (Month(ART.CREATEDDATETIME))<=DATEPART(week,@week2)

But how to fix DATEPART(week,@week2) > DATEPART(week,@date)

@date = @aaret2 + @week2 + 'first day of @week2'

Ioannis Paraskevopoulos

Hi,

Are ou sure this cursor is gonna work? In the For you select only version_id but you fetch into three variables.

Giannis

Ioannis Paraskevopoulos

Do you have any DATETIME variables or fields? DATEPART needs a datetime. If you have the year, month and day numbers you can create the datetime as

SELECT CONVERT(DATETIME,CONVERT(VARCHAR(@YEAR)+RIGHT('00',CONVERT(VARCHAR(@MONTH),2)+RIGHT('00',CONVERT(VARCHAR,@DAY),2))

Open in new window

Giannis

team2005

ASKER

Hi!

Need a way to get first day of week, from
Year + week

Ioannis Paraskevopoulos

Can you explain how do you want that? I mean is Jan 1st always the first day of the first week of the year? Or is the first, let's say, Sunday of the year the first day of the first week?

Giannis

ASKER CERTIFIED SOLUTION

Ioannis Paraskevopoulos

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

team2005

ASKER

Hi!

This (SELECT @Date, DATEADD(wk,@WEEK-1,@Date)) gives me always the day -> 2 day of the week ?

Ioannis Paraskevopoulos

Hi,

Can you give me exact year and weekno?

Giannis

team2005

ASKER

Hi!

SET @YEAR = 2013
SET @WEEK = 7

Gives me date -> 2012-02-12
That is wrong Date

The right date is -> 2012-02-11

Ioannis Paraskevopoulos

Are you sure?
Jan 1st 2013 was Tueday, so you are expecting a Tuesday, right? 2012-02-11 is a Monday.

Giannis

Ioannis Paraskevopoulos

It all dpends of how do you define the first week of the year.

In your case what is the first day of the year,
what is the first dat of a week
and how many days are in the first week of 2013.

I belive that in your case the answers are:

the first da of the year is Tue Jan 1st
The first day of the week is Monday
And the first week of 2013 had 6 days.

Am i right?

team2005

ASKER

Hi!

Yes, you have right

Ioannis Paraskevopoulos

I ope this helps:

DECLARE @YEAR AS INT
DECLARE @WEEK AS INT
DECLARE @Date AS DATETIME
DECLARE @FirstDateOfFirstWeek AS DATETIME

SET @YEAR = 2013
SET @WEEK = 1
SET @Date = CONVERT(VARCHAR,@YEAR)+'0101'
SET @FirstDateOfFirstWeek  = DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date)

IF @WEEK = 0 
	BEGIN
		SELECT @Date
	END
ELSE
	BEGIN
		SELECT DATEADD(wk,@WEEK-1,@FirstDateOfFirstWeek)
	END

Open in new window

Giannis

team2005

ASKER

thanks