SQL to give Friday of running week — oracle-tech

    Forum Stats

  • 3,714,818 Users
  • 2,242,634 Discussions
  • 7,845,078 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

SQL to give Friday of running week

karthick.raje
karthick.raje Member Posts: 14 Green Ribbon

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

Tagged:

Best Answer

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,163 Silver Trophy
    edited January 13

    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.

  • RogerT
    RogerT Member Posts: 1,850 Gold Trophy

    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
    Solomon Yakobson Member Posts: 18,058 Black Diamond

    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
    RogerT Member Posts: 1,850 Gold Trophy

    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.

Sign In or Register to comment.