SQL to give Friday of running week

karthick.raje

we need SQL which gives Friday of running week with below conditions

If we run SQL on Monday and Tuesday of a week, SQL should return previous week Friday.

If we run SQL on Wednesday, Thursday and Friday ,SQL should return current week Friday.

example

If we run on 11th and 12 of January SQL should give 8th January

if we run on 13th, 14th and 15th of January, should give 13th January

Thanks in advance

Karthick

Frank Kulash

Hi,

You can use TRUNC (dt - 2, 'IW') + 4 , like this:

WITH  sample_data  AS
(
  SELECT DATE '2020-12-31' + LEVEL AS dt
  FROM   dual
  CONNECT BY level <= 20
)
SELECT   dt
,        TRUNC (dt - 2, 'IW') + 4  AS friday
FROM     sample_data
ORDER BY dt;

Output:

TRUNC returns the Monday of the ISO week. Adding 4 days to Monday gives you Friday.

The ISO week starts on Monday. Since your "fiscal week" begins on Wednesday, 2 days later that the ISO week, we subtract 2 days before getting the appropriate Monday.

Beware of using the NEXT_DAY function: it depends on your NLS settings. It may produce the right results when you test it, but give different results when you use it on another database, or even in another session on the same database.

BEDE

Try something as follows:

with parms as(

 select to_date('13-jan-2021','dd-mon-yyyy') crt_dt

 from dual

)

,td as (

select trunc(crt_dt,'dd') crt_dt

 ,trim(to_char(crt_dt,'day')) crt_wkday

from parms 

)

select

 crt_dt

 ,crt_wkday

 ,case

   when crt_wkday in ('monday','tuesday','saturday','sunday') then

    next_day(crt_dt-5,'friday')

   else next_day(crt_dt,'friday')

 end x_friday

from td

;

I hope I understood well your requirements.

By the way: you did not specify what to do in case the day is Saturday or Sunday.

Frank Kulash

Hi,

You can use TRUNC (dt - 2, 'IW') + 4 , like this:

WITH  sample_data  AS
(
  SELECT DATE '2020-12-31' + LEVEL AS dt
  FROM   dual
  CONNECT BY level <= 20
)
SELECT   dt
,        TRUNC (dt - 2, 'IW') + 4  AS friday
FROM     sample_data
ORDER BY dt;

Output:

TRUNC returns the Monday of the ISO week. Adding 4 days to Monday gives you Friday.

The ISO week starts on Monday. Since your "fiscal week" begins on Wednesday, 2 days later that the ISO week, we subtract 2 days before getting the appropriate Monday.

Beware of using the NEXT_DAY function: it depends on your NLS settings. It may produce the right results when you test it, but give different results when you use it on another database, or even in another session on the same database.

RogerT

I would just always subtract 5 ...

with data (d) as (select date '2021-01-13' from dual union all -- Wed 15.1.
         select date '2021-01-12' from dual union all -- Tue 08.1.
         select date '2021-01-15' from dual union all -- Fri 15.1.
         select date '2021-01-16' from dual) -- Sat 15.1.?
select next_day(d - 5,'Friday') 
 from data;

hth

Solomon Yakobson

https://community.oracle.com/tech/developers/discussion/comment/16781202#Comment_16781202

Not every client is english speaking client::

alter session set nls_date_language=lithuanian
/
with data (d) as (select date '2021-01-13' from dual union all -- Wed 15.1.
         select date '2021-01-12' from dual union all -- Tue 08.1.
         select date '2021-01-15' from dual union all -- Fri 15.1.
         select date '2021-01-16' from dual) -- Sat 15.1.?
select next_day(d - 5,'Friday')
 from data
/
                      *
ERROR at line 5:
ORA-01846: not a valid day of the week

SQL>

SY.

RogerT

I tested it with german ... but the bad thing is, that "Freitag" and "Friday" are compatible as the day string is different but the abbreviation of the day matches...

So, your solutions is perfect - mine and the one of Bede could be improved by using with data (d) as (select date '2021-01-13' from dual union all -- Wed 15.1.

         select date '2021-01-12' from dual union all -- Tue 08.1.
         select date '2021-01-15' from dual union all -- Fri 15.1.
         select date '2021-01-16' from dual) -- Sat 15.1.?
select next_day(d - 5,to_char(date '2021-01-15','Day')) /*
 from data
/

so that the day string matches the language set in the environment.