SELECT Ip.Adm_Date + Ip.Adm_Time / 86400 AS Admdatetime  
     , Ip.Pt_Code  
     , Cpi.Surname  
     , Cpi.First_Given_Name  
     , Ip.W_Code
     , ''as "Reviewed/No MR completed   "
     , '' AS "MR < 24"  
     , '' AS "MR 24-36"  
     , '' AS "MR 36-48"  
     , '' AS "MR 48-60"  
     , '' AS "MR 60-72"  
     , '' AS "MR>72"  
     , '' AS "MR Finalised"
     , '' AS "MR on Dx"  
     , '' AS "Pharm/Tech"  
     , '' AS "Met MR Criteria" 
     , '' as "Comments" 
  FROM K_Ipreg Ip, K_Cpireg Cpi 
 WHERE Ip.Pt_Code = Cpi.Pt_Code  
   AND Ip.Disch_Date IS NULL  
   AND Ip.Adm_Date BETWEEN ( ( CASE  
                                 WHEN TO_CHAR ( SYSDATE, 'd' ) = 1  
                                 THEN SYSDATE - 4  
                                 ELSE SYSDATE - 2  
                              END ) )  
                       AND SYSDATE  
   AND W_Code NOT IN ('ICW', 'NWB', 'ANTE', 'BABY', 'CHILD', 'DELI', 'ED', 'MATY', 'SCBU')

SQL> alter session set NLS_TERRITORY=AMERICA;

Session altered.

SQL> select to_char(sysdate,'D') d from dual;
D
-
2

1 row selected.

SQL> alter session set NLS_TERRITORY=FRANCE;

Session altered.

SQL> select to_char(sysdate,'D') d from dual;
D
-
1

1 row selected.

  CASE  WHEN TO_CHAR ( SYSDATE, 'd' ) = 1 THEN SYSDATE - 4  ELSE SYSDATE - 2  END and SYSDATE 

 AND Ip.Adm_Date BETWEEN 
     decode(TO_CHAR ( SYSDATE, 'DY' ),'MON',TRUNC(SYSDATE - 4),TRUNC(SYSDATE - 2)) 
     AND TRUNC(SYSDATE)

SELECT TO_CHAR ( SYSDATE, 'd' ) from dual

SQL> select to_char(sysdate,'Dy','NLS_DATE_LANGUAGE=ENGLISH') en,
  2         to_char(sysdate,'Dy','NLS_DATE_LANGUAGE=FRENCH') fr,
  3         to_char(sysdate,'Dy','NLS_DATE_LANGUAGE=GERMAN') ge
  4  from dual
  5  /
EN  FR   GE
--- ---- --
Tue Mar. Di