dokuwiki

alter session set NLS_DATE_FORMAT='yyyymidd';

select last_name, hire_date from employees where 
to_char(hire_date, 'yyyymmdd') 
between '19980201' and  '19980701'
/

nachteil JEDE zeile mu� konvertiert werden

select last_name, hire_date from employees where 
hire_date 
between to_date('19980201', 'yyyymmdd') and  to_date('19980701', 'yyyymmdd') 
/

select * from (
select first_name, last_name, hire_date, trunc(months_between( sysdate, hire_date)) asd from employees
)
where asd >=120
order by hire_date

Zahl der Tage pro Monat, ACHTUNG das trunc von last_day liefert Mitternacht des letzen Tages, also dauert der Monat dann noch 24h!

select (trunc(last_day(sysdate))+1 - trunc(sysdate, 'month')) from dual;

select add_months(to_date('20090131', 'yyyymmdd'), 1) from dual;

select to_date('15821004', 'yyyymmdd') + 1 from dual;

select to_char(sysdate, 'yyyymmdd hh24miss')  from dual;

select sysdate from dual;


select first_name, 
       last_name
   --    to_char(hire_date, 'yyyymmdd')  
       from employees
       where to_char(hire_date, 'yyyy') BETWEEN 1995 and 1997
       order by hire_date
       ;
  

alter session set NLS_DATE_FORMAT='yyyymmdd'     

select first_name, 
       last_name,
       to_char(hire_date, 'yyyymmdd')  
       from employees
       where hire_date 
       BETWEEN to_date('19950101','yyyymmdd') and to_date('19980101','yyyymmdd')      
       order by hire_date
       ;

select first_name, 
       last_name,
       trunc(sysdate - hire_date) tage
       from employees
       order by hire_date
       ;
       
select to_char(to_date('2010', 'yyyy'), 'yyyymmdd hh24mi') from dual


select sysdate-1/24 from dual;

select extract(month from sysdate) from dual

select first_name, 
       last_name,
       to_char(hire_date, 'yyyymmdd')  
       from employees
       where extract(year from hire_date)
       BETWEEN 1995 and 1997
       order by hire_date
       ;