Forum Stats

  • 3,768,176 Users
  • 2,252,755 Discussions
  • 7,874,483 Comments

Discussions

Remove the Sunday and Saturday from data

User_7PZDE
User_7PZDE Member Posts: 282 Blue Ribbon
edited May 26, 2017 10:19AM in SQL & PL/SQL

Hi Team

Can please help i have data with 1000 records like so_id & Date in that data need to remove Saturday and Sunday records using

sysdate

i.e please find the below DDL and DML script.

Create table revenue_t (

So_ID number,

So_Date Date

);

insert into table revenue_t values(1,'12-05-17'); --Sat

insert into table revenue_t values(1,'13-05-17'); --San

insert into table revenue_t values(1,'14-05-17');

insert into table revenue_t values(1,'15-05-17');

insert into table revenue_t values(1,'16-05-17');

insert into table revenue_t values(1,'17-05-17');

insert into table revenue_t values(1,'18-05-17');

insert into table revenue_t values(1,'19-05-17'); --Sat

insert into table revenue_t values(1,'20-05-17'); --San

insert into table revenue_t values(1,'21-05-17');

insert into table revenue_t values(1,'22-05-17');

Thanks in Advance

Tagged:
Mustafa_KALAYCIGaz in OzmathguyUser_7PZDE

Answers

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited May 26, 2017 4:40AM

    Not sure what do you mean by "using SYSDATE". You may b looking for something like

    SELECT/DELETE FROM revenue_t

    WHERE   TO_CHAR ( So_Date

                            , 'DY'

                            , 'NLS_DATE_LANGUAGE=ENGLISH'

                            )   IN ('SAT', 'SUN')

    Mustafa_KALAYCI
  • Srinivas Vanahalli
    Srinivas Vanahalli Member Posts: 81
    edited May 26, 2017 4:45AM

    Also your insert statements need date coversion like below.

    insert into  revenue_t values(1,to_date('12-05-17','dd-mm-yy'));

    edited: Also remove table keyword from insert statements.

  • Nuno R
    Nuno R Member Posts: 158 Red Ribbon
    edited May 26, 2017 5:30AM

    SELECT TO_DATE('26-05-2017','DD-MM-YYYY') MYDATE,TO_CHAR(TO_DATE('26-05-2017','DD-MM-YYYY'),'D') DAYOFWEEK FROM DUAL;

    -- INSERT ONLY WHEN DAYOFWEEK IN (2,3,4,5,6)

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,368 Bronze Crown
    edited May 26, 2017 5:39AM

    I guess this would be one of the biggest mistakes. 28/05/2017 is sunday.

    alter session set NLS_TERRITORY=AMERICA;select to_char(to_date('28/05/2017','dd/mm/yyyy') , 'D') from dual;result:1alter session set NLS_TERRITORY=TURKEY;select to_char(to_date('28/05/2017','dd/mm/yyyy') , 'D') from dual;result: 7

    day of week could be changed for territory.

    Gaz in Oz
  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited May 26, 2017 6:01AM

    Something like this ?

    select to_char(rt.so_date,'DAY'), rt.*from revenue_t rtwhere to_char(rt.so_date,'fmDAY') NOT IN ('SATURDAY','SUNDAY');

    You can change this to a DELETE query to remove Saturdays and Sundays.

    Note: the FM (format modifier) used. Remove it and check the result.

    Below will also give the same result:

     where RTRIM(to_char(rt.so_date,'DAY')) NOT IN ('SATURDAY','SUNDAY');
    Mustafa_KALAYCI
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    edited May 26, 2017 7:21AM

    First of all, '12-05-17' is string, not date. Use either TO_DATE('12-05-17','mm-dd-rr') or better use date literals DATE '2017-12-05'. Anyway, simplest solution to select / delete Saturdays & Sundays would be using ISO week format IW which assumes week always starts Monday:

    SQL> select  *

      2    from  revenue_t

      3    where so_date - trunc(so_date,'iw') >= 5

      4  /

         SO_ID SO_DATE

    ---------- --------

             1 13-05-17

             1 14-05-17

             1 20-05-17

             1 21-05-17

    SQL> delete revenue_t

      2    where so_date - trunc(so_date,'iw') >= 5

      3  /

    4 rows deleted.

    SQL>

    SY.

    Gaz in Ozmathguy
  • S567
    S567 Member Posts: 418 Red Ribbon
    edited May 26, 2017 9:47AM

    I agree with your logic,even i tried the same with out RTRIM  but results are not as expected...when used rtrim the results are good.

    May i know why RTRIM is needed for to_char(so_date,'DAY') ?

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,906 Red Diamond
    edited May 26, 2017 10:19AM

    Check what format DAY returns - "Name of day, padded with blanks to display width of the widest name of day in the date language used for this element". But it is better to use FM modifier: TO_CHAR(dt,'FMDAY'). Next step is DAY returns NLS dependent day name. For example, client in Germany will see SONNTAG, not SUNDAY. You need to use TO_CHAR third parameter NLS_DATE_LANGUAGE=ENGLISH to get SUNDAY regardless of client's NLS settings. But it is much simpler to use IW format as in mt reply.

    SY.

    User_7PZDEUser_7PZDE
This discussion has been closed.