No announcement yet.

How to calculate week number for month in CLLE?

  • Filter
  • Time
  • Show
Clear All
new posts

  • How to calculate week number for month in CLLE?

    Please help me out as i want to automate a task and main part is calculating week number of month. I have tried a logic where you retrieve 1st day of week of the month and then add to the current date and divide by 7.
    It seems to work just fine but gets stuck on some days when the week starts with Sunday.

  • #2
    What definition of "week number" are you using?

    Jan 1 2017 was a Sunday, so Jan 4 2017 was Wednesday. That would mean that Jan 4 2017 should be Week #1.

    But Jan 1 2016 was a Friday, so how many days were in Week #1 in 2016? Some of the days in that week were in 2015 and the rest in 2016. Does that week therefore have two Week #s?

    Also, Dec 31 2017 will be a Sunday, so what will its Week # be? Is Jan 1 2018 in a different Week #?

    Calculating "Week #" requires a very specific set of rules. Different rules gives different results. Tell us your rules and show us your CL so we can suggest changes.

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?


    • #3
      The VAR_CHARFORMAT SQL Scalar function can calculate the week number in a month, as well as the Week in the year (Jan, 1st is always in the first week) and the week accoriding the ISO guidelines (Week starts with monday and in the first week of the year the majority of days must be from the new year, i.e. January, 4th is always in the 1st Week).

      VarChar_Format(YourTimestamp, 'W') --> Returns the Week in a month
      VarChar_Format(YourTimestamp, 'WW' --> Returns the Week of the year
      VarChar_Format(YourTimestamp, 'IWW') --> Returns the Week according the ISO guide lines.
      If you want to use those results in CL, you can create a (temporary) view using the RUNSQL CL-Command.
      And the just read the result from the view like from any file or table

      Example for creating the view
      Create View qtemp/Myview (WeekMon)
      Values(VarChar_Format('2017-04-19-', 'WW'))


      • #4

        Cool trick - but missing '' AS '

        Create View qtemp/Myview (WeekMon) as                    
        Values(VarChar_Format('2017-04-19-', 'WW'))