nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Current week of the year

3174977

Hi All,

I want to know the logic how to get the current week of the year (June 26th - July 2nd). I don't want week number but I wanted to dynamically display the current week data .

Thanks !

Find more posts tagged with

Accepted answers

All comments

Frog Toad

Hi!

SELECT
  TIMESTAMPADD(
       SQL_TSI_DAY
       , 1
       , TIMESTAMPADD(SQL_TSI_DAY
            , DAYOFWEEK(CURRENT_DATE) * -1
            , CURRENT_DATE
       )
   )
  , TIMESTAMPADD(
       SQL_TSI_DAY
       , 7
       , TIMESTAMPADD(SQL_TSI_DAY
            , DAYOFWEEK(CURRENT_DATE) * -1
            , CURRENT_DATE
       )
   )
FROM [YOUR_SUBJECT_AREA]

3174977

Hi,

From subject area means ? What actually do i need to give here. I am planning to create a dynamic variable based on this SQL. Can I do that ?

It is saying TIMESTAMPADD invalid identifier

Thanks !

Frog Toad

It was sql-query for OBI.

If you want make dynamic variable, you need use classic sql:

ALTER SESSION SET NLS_TERRITORY = AMERICA;

SELECT TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE,'D'))
    , TRUNC(SYSDATE) - TO_NUMBER(TO_CHAR(SYSDATE,'D')) + 7
FROM DUAL;

This 'D'-element returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

The best solution, not to depend on regional settings - you need make table "Calendar", where on each date you can assign day number of week (and week number, and year, and a lot more)

After that, you can get dates using simple query, example

SELECT DATE
FROM CALENDAR
WHERE YEAR = 2016
    AND WEEK_NUM = 24
    AND DAY_OF_WEEK_NUM IN (1, 7)

3174977

Hi,

I used the same but it is just giving only 2 days instead of the whole week

But with this filters will this change dynamically everytime ? because whenever the user logins they wanted to see current week and further data only

Thanks !

Frog Toad

You have table "Calendar", good.

But the field CALENDAR_DAY_of_the_week filled not as you need

Now you need to fill in the fields of the table so that 26.06.2016 was the first day of week, 02.07.2016 was the seventh day of week (most likely need to use a new column)

This query is dinamical:

SELECT DATE
FROM CALENDAR
WHERE YEAR =  EXTRACT (YEAR FROM SYSDATE)
    AND WEEK_NUM = TO_NUMBER(TO_CHAR(SYSDATE, 'WW'))
    AND DAY_OF_WEEK_NUM IN (1, 7)

Thomas Dodds

Put the textual representation of the week in your calendar dimension for each day ... a well-formed calendar dim will serve you well time and time again.

deeps

You can use Connect By Level to display all the days within current week. you need to find the current week from your Day table in warehouse.

refer these:

List of days between two dates

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:14582643282111

Upcoming Event

Oracle AI World 2026
Where AI meets business outcomes Oracle AI World brings the Oracle community together to explore how AI and other technology innovations are driving real business outcomes across industries. Discover what’s new through: Mainstage keynotes to hear the latest directly from Oracle leadership Product demonstrations Customer…

Top Leaders