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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

How to build date ...

Hi!

Have a sql that lock like this:

Declare
@StartDate datetime,
@EndDate datetime,
@Date datetime,
@SQL varchar(2000),
@version_id varchar(50),
@MyCursor CURSOR,
@@perioden varchar(4),
@moneden varchar(2),
@aaret1 varchar(2),
@aaret2 int,
@moneden2 int

Set @StartDate = '2012/10/01'
Set @EndDate = '2015/12/31'
Set @Date = @StartDate


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

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @version_id
                                           
WHILE @@FETCH_STATUS = 0              
BEGIN
    
   
  -- Hent ut uke+år fra perioden -> setts i 2 felter
  SET @perioden = SUBSTRING (@version_id ,3 , 4)
  SET @moneden = SUBSTRING (@perioden ,1 , 2)
  SET @aaret1 = SUBSTRING (@perioden,3,2)
  SET @aaret2 = @CAST(@aaret1 AS INT)
  SET @moneden2 = @CAST(@moneden1 AS INT)
   
  Set @Startdate = ?
  Set @Endate = ? 
  Set @Date = @StartDate
     
-- Loop through dates
  WHILE @Date <=@EndDate
  BEGIN
 
    -- Insert record in dimension table
    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgno' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgse' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgdk' 
    )

    INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'hgfi' 
    )

   INSERT Into Perioder_tmp_perioder
    (    
    [version_id], 
    [FORECASTYEARWEEK],
    [DATAAREAID]
    )
    Values
    (
    @version_id,
    CAST(YEAR(@Date) AS nvarchar(4))+REPLICATE('0', 2 - LEN(CAST(DATEPART(week,
    @Date) AS nvarchar (2)))) + CAST(DATEPART(week,@Date) AS nvarchar (2)),
    'vvv' 
    )

-- Goto next day
   Set @Date = @Date + 7
  END
   FETCH NEXT FROM @MyCursor
   INTO @version_id
   Set @Date = @StartDate
END

Open in new window



My problem is how to set the @Date -> @startdate and @EndDate
regarding to @perioden that get this values:

PU0212
PU0612
PU4412
PU0313

Example : PU0212 , where 02 = month and 12 is the year

The dates i want from this is 16 weeks in time
so the result from my example wil be:

0212
0312
0412
0512
0612
0712
0812
0912
..
..
1812

But the stardate must be converted to date, like -> 2012-02-01 -> 01 is the first day of week 02...

Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18

How can i do this ?
0
team2005
Asked:
team2005
  • 5
  • 3
1 Solution
 
LowfatspreadCommented:
you aren't making this clear

PU0212
PU0612
PU4412
PU0313

Example : PU0212 , where 02 = month and 12 is the year


so what is the 44 in pu4412?

its also not clear what you mean by this as the results....
0212
0312
0412
0512
0612
0712
0812
0912
..
..
1812

But the stardate must be converted to date, like -> 2012-02-01 -> 01 is the first day of week 02...

Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18


and how 1812 becomes 2012-18-02  ...

please restate your problem....
and be clear about what date formats you are attempting to use  e.g iso, american, european,arabic

it may help if you provide some insight/example on what the actual "business" scenario  you are attempting to solve actually is...
0
 
team2005Author Commented:
Hi!

Try to explane this:

Have a customer that have a forcast modell, and have periods like PU0212 or PU4412...

I must based on this values, insert 16 records on every PUxxxx

PU4412 -> Week = 44 and year =2012

So i must insert 16 records on every PUxxxx

Dates here wil be -> 0212 -> record 1      0312 -> record 2
0412 -> record 3    0512 -> record 4 e.t.c.....
counted 16 weeks .. the final record for PU0212 will be -> 1812 -> 2012-18-?

If i have PU5212 it must check if week >=52, and start from 01 if total of weeks is 52>

PU5212 gives this dates :

5212 - 0113 - 0213 ...... 1513    -> 16 weeks

Hope this explane this better
0
 
LowfatspreadCommented:
somewhat ... ok so the first two numbers are a week number not month...

so now you need to tell us which defintion of week you are wanting to use (e.g. iso or microsoft)  ... your week 52 example also doesn't really address what happens (how you want to deal with) the 53rd week of every year (each year has 52 full weeks plus 1 or 2 additional days), and if there are any special considerations for leap years...

and then which day do you wish to consider as the start/end day of a week...

and i still find this ambiguous
Last date -> 1812 -> 2012-18-02 -> 02 is the first day of week 18
what "date" representation is 2012-18-02   yyyy-dd-mm ? or is it your own internal usage  yyyy-wk-dy?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
team2005Author Commented:
Hi!

Valid range of weeks are -> 1...52

So if week goes from 52 to 01, the year must increment by one.

Start day of the week -> the first day
End day of the week -> last day of the week
0
 
LowfatspreadCommented:
ok lets try again

pu0113  is week 1 year 2013

so when does that week start?
 01 Jan 2013 ie Tuesday   ending 06 jan 2013 ie Monday
    all other weeks in 2013 starting on Tuesdays and ending on Mondays

and  pu5213 week 52 year 2013
starts tuesday 24th december and ends monday 30th december
tuesday 31 december 2013 isn't part of any week...
because pu0114 week 1 year 2014
starts wednesday 1 january and ends tuesday 6 january ....
0
 
team2005Author Commented:
I have solved this myself
0
 
team2005Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for team2005's comment #a38888415

for the following reason:

solved by myself
0
 
team2005Author Commented:
give you some points, for trying to help me
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now