Auswahl  

Datumskonvertierungen und Datumsformate in Oracle 

Oracle
PL/SQL:SQL
RDBMS 10.x
09.11.17 (MP)
15.06.18 (MM)
to_date,to_char, Datum

Body

Wer hat nicht schon mal vor dem Problem einer Datumskonvertierung bei Oracle Datenbanken gestanden?
Da kann sehr viel Zeit verstreichen, bis man eine Lösung gefunden hat. Wir wollen Ihnen hier ein Paar Fallstricke erklären.

Welches Datumsformat wird als Default ausgegeben?

Das hängt vom Parameter nls_date_format ab, der auf Clientseite im Betriebssystem gesetzt wird.

z.B. können Sie unter DOS in einem Fenster angeben:

dos> set NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
dos> sqlplus scott/tiger

SELECT sysdate FROM dual;

SYSDATE
-------------------
15.06.2018 11:12:59


Für Linux:

$> export NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS'
$> sqlplus scott/tiger

SELECT sysdate FROM dual;

SYSDATE
-------------------
15.06.2018 11:12:59


Im SQL*Developer kann stattdessen auch unter Extras/Voreinstellungen/Datenbank/NLS das Datumsformat eingestellt werden.

Bild1 Anzeige

Die Konvertierung in einem SQL-Befehl

Wenn Sie sich in einzelnen Befehlen nicht an den Default halten möchten, können Sie eine Ausgabeformatierung verwenden:

SELECT to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') FROM dual;


Achten Sie darauf, dass die Minuten mit "MI" gekennzeichnet sind. Für Oracle wäre auch folgendes  Format OK:

SELECT to_char(sysdate,'Day.Month.Year HH:MM:SS') AS datum FROM dual;

DATUM
--------------------------------------------
Friday   .June     .Twenty Eighteen 11:06:17


Nur leider ist das Format falsch, weil im String "HH:MM:SS" Stunden:Monat:Sekunden ausgegben werden, und das stimmt nur ganz selten mit der tatsächlichen Uhrzeit überein (eine Minute pro Stunde :-)

Es gibt eine riesige Liste an möglichen Datumskonvertierungen die hier nachgelesen werden können.

Nur ein paar Beispiele für Oracle Datumsformate:

  • DD   Tagnummer (0-31)
  • DAY Tagesname  (Montag)
  • DY Tagesname, kurz (DO)
  • DDD Tagesnummer im Jahr (0-366)
  • MM   Monat (1-12)
  • MONTH   Monatsname ( Mai)
  • YY  Jahreszahl zweistellig
  • YYYY Jahreszahl vierstellig

Datum für Oracle eingeben

Wenn Sie ein Datum in die Datenbank einpflegen möchten, sollten Sie das idealerweise auch mit einer expliziten Konvertierung tun.

Beispiel:

INSERT INTO emp (empno,ename,hiredate) VALUES
  (8000, 'MARCO', to_date('30.12.2017','DD.MM.YYYY'));


Hier werden Stunden, Minuten und Sekunden auf 0 gesetzt. Das spart zwar keinen Speicherplatz, erleichtert aber später die Suche nach einem Einstellungstag. Wenn Sie stattdessen schreiben:

INSERT INTO emp (empno,ename,hiredate) VALUES (8000, 'MARCO', sysdate);


Dann wird die aktuelle Uhrzeit im Datum mit gespeichert. Jetzt versuchen Sie mal nach Mitarbeitern zu suchen, die heute eingestellt wurden:

SELECT * FROM emp WHERE hiredate=sysdate; -- FALSCH
SELECT * FROM emp WHERE trunc(hiredate)=trunc(sysdate); -- RICHTIG


Nur sollte es einen normalen Index auf der Spalte hiredate geben, wird er nicht benutzt werden. Deswegen könnte man folgende Variante benutzen:

SELECT * FROM emp
 WHERE hiredate BETWEEN trunc(sysdate)
                    AND trunc(sysdate+INTERVAL '1' HOUR - INTERVAL '1' SECOND);


Hier suchen Sie nach allen Mitarbeitern, die zwischen 00:00.00 und 23:59:59 des aktuellen Tages eingestellt wurden.

 

Datumskonvertierungen und Fallstricke in Oracle von to_char über to_date

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.