Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Sri.

Asked: July 15, 2025 - 9:15 pm UTC

Last updated: July 17, 2025 - 12:37 pm UTC

Version: 12

Viewed 100+ times

You Asked

I want to calculation week number concatenated with year as yyyy
My week starts from Saturday to Friday
Example: week 1 for year 2025 will be from December 28th 2024 to 3rd jan 2025

Week 1 for year 2024 will be 30th December 2023 to 5th jan 2024
Please help me how this can be achieved

and Chris said...

ISO weeks start on Monday. The first ISO week of a year is the one that contains the first Thursday.

So you can get what you want by adding two to a date. This effectively shifts it two days, so Saturdays become Mondays. Then find the ISO week/year for this date.

For example, this returns the Fridays and Saturdays at the start and ends of 2023-2025. Adding two to the date and using the IW-IYYY (ISO week and year) returns the values you want:

with start_date as (
  select date'2022-12-31' as dt 
  from   dual
), dates as (
  select dt + level as dt, 
         to_char ( dt + level, 'dy' ) as dy, 
         to_char ( dt + level, 'mon' ) as mnth
  from   start_date
  connect by level <= 750
)
select d.*,
       to_char ( dt + 2, 'iw-iyyy' ) week_year,
       extract ( day from dt ) d
from   dates d
where  dy in ( 'fri', 'sat' ) 
and    (
  ( mnth = 'dec' and extract ( day from dt ) > 24 ) or 
  ( mnth = 'jan' and extract ( day from dt ) < 8 )
);

DT                   DY           MNTH         WEEK_YE          D
-------------------- ------------ ------------ ------- ----------
06-JAN-2023 00:00:00 fri          jan          01-2023          6
07-JAN-2023 00:00:00 sat          jan          02-2023          7
29-DEC-2023 00:00:00 fri          dec          52-2023         29
30-DEC-2023 00:00:00 sat          dec          01-2024         30
05-JAN-2024 00:00:00 fri          jan          01-2024          5
06-JAN-2024 00:00:00 sat          jan          02-2024          6
27-DEC-2024 00:00:00 fri          dec          52-2024         27
28-DEC-2024 00:00:00 sat          dec          01-2025         28
03-JAN-2025 00:00:00 fri          jan          01-2025          3
04-JAN-2025 00:00:00 sat          jan          02-2025          4


Note this may not always be exactly what you want. When the 1st Jan is a Friday, the formula shifts it to Monday 3rd Jan. This falls in ISO week 53 of the previous year. For example, Sat 26 Dec 2020 and Fri 1 Jan 2021 both end up in week 53 of 2020 instead of week 1 of 2021:

select 
  to_char ( date'2020-12-26' + 2, 'iw-iyyy' ) sat,
  to_char ( date'2021-01-01' + 2, 'iw-iyyy' ) fri; 

SAT     FRI    
------- -------
53-2020 53-2020


If you always want the last Saturday of the year to be the first of the next, you'll have to tweak this logic.

Rating

  (1 rating)

Comments

Different interpretation of requirement

mathguy, July 16, 2025 - 5:18 pm UTC

Hello Sri,

Your question should be more specific. What are you looking for? Do you have several dates in a table, and you want a query that - for each date - calculates the week number and week-based year (not necessarily the same as calendar year)? Or do you need a function (PL/SQL) that takes a date as input, and returns a string in the format WW-YYYY showing the week number and week-based year? Or do you need to create a lookup table, having week number, "year", starting Saturday date and ending Friday date? Or anything else?

You should also give a complete definition of "first week of week-based year". You gave two examples, which is good, but not a complete definition. From the examples, we can see already that your definition is not similar to the ISO definition of "week of the year". In the ISO definition, a week at the end of one year and beginning of next year always belongs to the year that has more of the days in that week (four or more days). This is the same as saying "if the Thursday of that week - a week is always from Monday to Sunday, with Thursday the middle day - falls in 2023 then the whole week is considered as part of 2023, as the last week of that year; but if the Thursday is in 2024, then the whole week becomes the first week of 2024." In your example, even if a week has four days in the "old" year and only three in the "new" year, it is still considered as part of the "new" year.

Below I will assume you have several dates in a single column in an input table, and you need a query that will show, in the output, the date, the week-based year, and the week number in that year. I show the "year" and the "week number" in separate columns - concatenating isn't the issue. To mimic the input table, I use a subquery in the WITH clause (the first one); in your case you wouldn't have that there, and instead of selecting from SAMPLE_INPUTS in PREP you would select from your actual table.

For each date, the query finds January 1st for that date's calendar year, and also January 1st of the following year. From these, a computation using ISO week (so that the computation is independent of locale) finds the beginning of the first week of the "current" year as well as that of the "next" year; this is done by shifting forward by two days, finding the Monday of the week and then shifting back two days. Then we compare the input date to the "first Saturday of the next week-based year" (which may still be in the current CALENDAR year!) to decide in which week-based year the data falls. And then the week number is either 1 (if the date falls in the next week-based year), or it is computed by comparing to the "first Saturday of the current year".

Note that in my example the dates don't have a time-of-day component (actually they do, but it's always 00:00:00); but the computation works without changes even if you have non-zero time of day in your input dates.

alter session set nls_territory='America';
alter session set nls_date_format='dd-MON-yyyy Dy';
with
  sample_inputs (dt) as (
    select    date '2024-12-24' + level
      from    dual
      connect by level <= 12
    UNION ALL
    select    date '2025-12-24' + level
      from    dual
      connect by level <= 12
  )
, prep (dt, cal_yr, fsoy_crt, fsoy_next) as (
    select dt,
           extract (year from dt),
           trunc(trunc(dt, 'year') + 2, 'iw') - 2, 
           trunc(add_months(trunc(dt, 'year'), 12) + 2, 'iw') - 2
    from   sample_inputs
  )
select dt,
       case when dt < fsoy_next then cal_yr else cal_yr + 1 end as wby,  -- wby for week-based year
       case when dt < fsoy_next then 1 + trunc((dt - fsoy_crt)/7) else 1 end as wk
from   prep
;


Output:

DT                 WBY   WK
---------------   ----   --
25-DEC-2024 Wed   2024   52
26-DEC-2024 Thu   2024   52
27-DEC-2024 Fri   2024   52
28-DEC-2024 Sat   2025    1
29-DEC-2024 Sun   2025    1
30-DEC-2024 Mon   2025    1
31-DEC-2024 Tue   2025    1
01-JAN-2025 Wed   2025    1
02-JAN-2025 Thu   2025    1
03-JAN-2025 Fri   2025    1
04-JAN-2025 Sat   2025    2
05-JAN-2025 Sun   2025    2
25-DEC-2025 Thu   2025   52
26-DEC-2025 Fri   2025   52
27-DEC-2025 Sat   2026    1
28-DEC-2025 Sun   2026    1
29-DEC-2025 Mon   2026    1
30-DEC-2025 Tue   2026    1
31-DEC-2025 Wed   2026    1
01-JAN-2026 Thu   2026    1
02-JAN-2026 Fri   2026    1
03-JAN-2026 Sat   2026    2
04-JAN-2026 Sun   2026    2
05-JAN-2026 Mon   2026    2

Chris Saxon
July 17, 2025 - 12:37 pm UTC

Good points; as you say, there the requirements are incomplete
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.