how to find a week number?

[freon]

ei guys got a another question,
i need some help here..

please help me find the specific week number of a specific date..


advance tanx..
have a niCe day (" ,

[arrow483]

Re: how to find a week number?

You will need to decide if your week starts on Sun or Mon. Also, what is the 1st week of the year?

Typically. I start with Jan 1st, determine day of week, add or subtract to get to the 1st Sunday. Then use %diff to gets days between, divide by 7 and add (or subtract) 1 to get it to come out the way I want.

[freon]

Re: how to find a week number?

whew! that's alot of computations there...

tanx sir arrow...
tanx alot...
godbless.....!

[tonyserzin]

Re: how to find a week number?

There is an international Standard (ISO 8601) that covers week numbers etc.

Week is defined as â??[a] time-interval of seven days, starting on a Monday and identified by its ordinal number within a calendar yearâ??

The week numbers are defined as â??the first calendar week of a year is the one that includes the first Thursday of that year and [...] the last calendar week of a calendar year is the week immediately preceding the first calendar week of the next yearâ??

[mjhaston]

Re: how to find a week number?

This will get you close, but I would check the week it returns because of the conversation above ....

      **********************************************************************
      * Procedure   - #dayOfYear                                           *
      * Description - Receive an *ISO date field and return the number     *
      *               of the day in the year.                              *
      * Input       - *ISO date field                                      *
      * Output      - Number 1 - 366                                       *
      **********************************************************************
     p #dayOfYear      b                   export

     d #dayOfYear      pi             3  0
     d inputDate                       d   value options( *nopass )

     d                 ds
     d workDate                        d   datfmt( *jul )
     d day                            3s 0 overlay( workDate : 4 )

      /free

        if %parms = 0;
          workDate = %date();
        else;
          workDate = inputDate;
        endif;

        return  day;

      /end-free

     p #dayOfYear      e

      /eject
      **********************************************************************
      * Procedure   - #weekOfYear                                          *
      * Description - Receive an *ISO date field and return the number     *
      *               of the week of the year.                             *
      * Input       - *ISO date field                                      *
      * Output      - Number 1 - 52.                                       *
      **********************************************************************
     p #weekOfYear     b                   export

     d #weekOfYear     pi             2  0
     d inputDate                       d   value options( *nopass )

     d workDate        s               d

      /free

        if %parms = 0;
          workDate = %date();
        else;
          workDate = inputDate;
        endif;

        return  %div( #dayOfYear( workDate ) : 7 ) + 1;

      /end-free

     p #weekOfYear     e 

[freon]

Re: how to find a week number?

sorry for L8t replies, coz we've been so busy this last few weeks.

tanx sir mjhaston
look'n forward of hearing from u again.

[B.Hauser]

Re: how to find a week number?

Hi,

the easiest way to determine the week no. is to use the SQL scalar functions WEEK or WEEK_ISO.

When using WEEK, a week starts with sunday and January, 1st is always in the first week of year.

D MyDate          S               D    inz(*Sys)
D MyWeek          S              2P 0
 *---------------------------------------------------------------
C/EXEC SQL  Set Option DatFmt = *ISO
C/END-EXEC
C/EXEC SQL  Set :MyWeek = Week(:MyDate)
C/END-EXEC
C     MyWeek        Dsply
C                   eval      *InLR       = *on 

According to the ISO Guidelines a week is defined as follows:
1. A week always starts with monday
2. The first week of year must have at least 4 days of the new year.
--> January 4th is always in the first week of year
--> The first thursday of a year is always in the first week of year.

SQL provides the scalar function WEEK_ISO, that determines a week according to the ISO Guidelines:

D MyDate          S               D    inz(*Sys)
D MyWeek          S              2P 0
 *---------------------------------------------------------------
C/EXEC SQL  Set Option DatFmt = *ISO
C/END-EXEC
C/EXEC SQL  Set :MyWeek = Week_ISO(:MyDate)
C/END-EXEC
C     MyWeek        Dsply
C                   eval      *InLR       = *on 

Birgitta

[Bernie]

Re: how to find a week number?

I found this nice piece of SQL - look at the bottom of this mail - posted by B. Hauser, that solved my problem in retrieving the ISO 8601 number of the week.
Is there any additional routine that also shows the year of the week?
Let me explain: December 31st, 2007 is in week number 1 of year 2008.
How can I retrieve the year 2008 when input is dec. 31st, 2007???
Any ideas ???

Bernie

D MyDate S D inz(*Sys)
D MyWeek S 2P 0
*---------------------------------------------------------------
C/EXEC SQL Set Option DatFmt = *ISO
C/END-EXEC
C/EXEC SQL Set :MyWeek = Week_ISO(:MyDate)
C/END-EXEC
C MyWeek Dsply
C eval *InLR = *on

[B.Hauser]

Re: how to find a week number?

Hi,

it's quite easy to determine the appropriate year, just add a simple select-statement:

/Free
   MyYear = %SubDt(MyDate: *Y);

   Select
   When MyWeekIso = 1 and %SubDt(MyDate: *M) = 12;
        MyYear += 1;
   When MyWeekIso >= 52 and %SubDt(MyDate: *M) = 1;
        MyYear -= 1;
   EndSL;
/End-Free 

Birgitta

[Johnm]

Re: how to find a week number?

I've just been trying this out for a new program I'm trying to write and it doesn't seem to be working quite correctly. When you run the code.....

D MyDate S D inz(*ISO)
D MyWeek S 2P 0
*---------------------------------------------------------------
C EVAL MyDate = %date
C/EXEC SQL Set Option DatFmt = *ISO
C/END-EXEC
C/EXEC SQL Set :MyWeek = Week(:MyDate)
C/END-EXEC


...the week number is calculated to be 37 when it should be 36 (todays date is 9th September 2011). Is there something I need to add to the SQL statment to fix this???

[vikramx]

Re: how to find a week number?

I believe SQL takes the starting day of the week as Monday and counts the weeks starting from there.

1st January, 2011 fell on a Saturday and it termed that week (27th December, 2010) as Week 1. Keeping that as the starting point, I would say 37 is correct.

[Johnm]

Re: how to find a week number?

Well ther you go, if I had spent another 30mins I wouldn't have needed to ask in the first place but this should be helpful for others, you need to enter

C/EXEC SQL Set :MyWeek = Week_ISO(:MyDate)

I you use Week rather than Week_ISO, it will not return the ISO week number even though you've specified 'Set Option DatFmt = *ISO'

Hope this is of help for someone else!!!

[Johnm]

Re: how to find a week number?

I believe SQL takes the starting day of the week as Monday and counts the weeks starting from there.

1st January, 2011 fell on a Saturday and it termed that week (27th December, 2010) as Week 1. Keeping that as the starting point, I would say 37 is correct.

Yes, that's what it's doing. But I needed the ISO week which is what most people use.

[B.Hauser]

Re: how to find a week number?

If you had read the complete thread you'd have seen my explication for both functions (WEEK and WEEK_ISO)

Birgitta

[Johnm]

Re: how to find a week number?

If you had read the complete thread you'd have seen my explication for both functions (WEEK and WEEK_ISO)

Birgitta

Yes, I now feel rather stupid!