Differences

This shows you the differences between two versions of the page.

--- kurs:datum [2008/10/23 12:25]
127.0.0.1 external edit
+++ kurs:datum [2014/09/10 21:22] (current)
@@ Line 1: / Line 1: @@
+alter session set NLS_DATE_FORMAT='yyyymidd';
+<code>
+select last_name, hire_date from employees where
+to_char(hire_date, 'yyyymmdd')
+between '19980201' and  '19980701'
+/
+</code>
+nachteil JEDE zeile mu� konvertiert werden
+<code>
+select last_name, hire_date from employees where
+hire_date
+between to_date('19980201', 'yyyymmdd') and  to_date('19980701', 'yyyymmdd')
+/
+</code>
+<code>
+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
+</code>
+Zahl der Tage pro Monat, ACHTUNG das trunc von last_day liefert Mitternacht des letzen Tages, also dauert der Monat dann noch 24h!
+<code>
+select (trunc(last_day(sysdate))+1 - trunc(sysdate, 'month')) from dual;
+</code>
+<code>
+select add_months(to_date('20090131', 'yyyymmdd'), 1) from dual;
+</code>
+<code>
+select to_date('15821004', 'yyyymmdd') + 1 from dual;
+</code>
+<code>
+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
+       ;
+</code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools