oracle - Trunc functiong example in sql -

i came across following sql query:

select case    when week >= to_char(trunc(to_date('{run_date_yyyymmdd}','yyyymmdd')-7,'d')+4,'iyyy-iw')     trunc(disconnect_date_datetime)   else trunc(disconnect_date_datetime,'d') end day some_table 

could please explain me usage of "trunc" function in above query, i'm not able understand it.

thanks

1. to_date('{run_date_yyyymmdd}','yyyymmdd') converts string date data type.
2. date_value - 7 subtracts 7 days date.
3. trunc( date_value, 'd' ) truncates date value midnight on first day of week (as determined nls_territory session parameter).
4. date_value + 4 adds 4 days date.
5. to_char( date_value, 'iyyy-iw' ) converts date the: 4-digit year based on iso standard; followed week of year (1-52 or 1-53) based on iso standard.

looking @ effect of nls_territory:

alter session set nls_territory = 'america';  select dt,        to_char(trunc(dt-7,'d')+4,'iyyy-iw') dt1,        to_char(dt-7,'iyyy-iw') dt2   ( select (sysdate + level - 1) dt dual connect level <= 7 );  alter session set nls_territory = 'france';  select dt,        to_char(trunc(dt-7,'d')+4,'iyyy-iw') dt1,        to_char(dt-7,'iyyy-iw') dt2   ( select (sysdate + level - 1) dt dual connect level <= 7 ); 

outputs:

session altered.   dt        dt1     dt2    --------- ------- ------- 05-apr-17 2017-13 2017-13 06-apr-17 2017-13 2017-13 07-apr-17 2017-13 2017-13 08-apr-17 2017-13 2017-13 09-apr-17 2017-14 2017-13 10-apr-17 2017-14 2017-14 11-apr-17 2017-14 2017-14   7 rows selected    session altered.   dt       dt1     dt2    -------- ------- ------- 05/04/17 2017-13 2017-13 06/04/17 2017-13 2017-13 07/04/17 2017-13 2017-13 08/04/17 2017-13 2017-13 09/04/17 2017-13 2017-13 10/04/17 2017-14 2017-14 11/04/17 2017-14 2017-14   7 rows selected  

you can see query gives different result when run on sundays (i.e. 09-apr-2017) when nls_territory setting changes america (where first day of week sunday) france (where first day of week monday).

if in territory first day of week monday can simplify query to:

to_char(dt-7,'iyyy-iw')