Thema: Oracle Datumsfunktion Datentyp Date

1 Frank Nussb cher 1 Thema: Oracle Datumsfunktion Datentyp Date Die Datumsfunktion liefert die Informationen zu Datum und Uhrzeit oder diese Werte manipuliert. Mit dem Datentyp Date wird eine alphanumerische Spalte definiert, in der Datumswerte abgelegt werden k nnen. Ein Datumswert ist eine spezielle Zeichenkette. Ein Datumswert ist mit anderen Datumswerten und mit Zeichenketten der Codeattribute ASCII und UNICODE Angabe des Datentyps Date erfolgt bei der Spaltendefinition. Eine Datumsfunktion (date_function) ist eine Funktion, die auf Datums- oder Zeitstempelwerten angewandt wird bzw. als Ergebnis einen Datums- oder Zeitstempelwert liefert.

2 Das Datum ist der h ufigste Datentyp , den Sie in einer Oracle -Datenbank finden. Beim Anlegen der customer-Tabelle h tten wir relativ einfach eine zus tzliche Spalte namens sale_date einf gen k nnen: SQL> create table customer 2 (last_name varchar2(30) not null, 3 state_cd varchar2(2), 4 sales number, 5 sale_date date); Table created. Bei Oracle enth lt der Datentyp Date tats chlich zwei Werte: das Datum und die Zeit. Daran sollte man beim Vergleich von zwei Datumsangaben unbedingt denken, da Oracle im Datum stets eine Zeitangabe speichert. Das standardm ige Datumsformat in Oracle ist DD-MON-YY, wobei DD der Tag, MON der Monat und YY der die zweistellige Jahresangabe ist.

3 Der Teil mit der Jahresangabe wird als vierstelliges Jahr gespeichert: die ersten beiden Ziffern sind standardm ig immer das aktuelle Jahr. Einsatz von Funktionen mit dem Datentyp Date Oracle stellt Ihnen die Manipulation des Datentyps Date eine lange Liste mit Funktionen zur Verf gung. Nehmen wir an, Sie m chten einem Kunden am letzten Tag des Monats eine Zahlungserinnerung senden. Wenn Sie den Brief zum richtigen Zeitpunkt ausdrucken und versenden m chten, setzten Sie zur bernahme des korrekten Datums in den Briefkopf die Funktion last_day ein. Tabelle 1 zeigt die gebr uchlichsten Datumsfunktionen. Spezielle Formate beim Datentyp Date Bei den Datumsangeben k nnen Sie mit unterschiedlichen Formaten arbeiten.

4 Diese Formate ndern das Anzeigeformat eines Datums. Tabelle 2 zeigt die Datumsformate und die zugeh rigen Ausgaben. Frank Nussb cher 2 Tabelle 1 // bliche Funktionen auf Datumsangaben Funktion R ckgaben Beispiel Anzeigen sysdate Aktuelles Datum und Zeit selsect sysdate from dual; 28-FEB-02 am 28 Februar 2002 last_day Letzter Tag des Monats select last_day(sysdate) from dual; 31-MAR-02 am 12 M rz 2002 add_months(d,n) Addiert oder subtrahiert n Monate vom Datum d select add_months(sysdate,2) from dual 18-MAY-02 am 18 M rz 2002 months_between(f,s) Differnenz in Monaten zwischen dem Datum f und dem Datum s select months_between(sysdate, `12-MAR-02`) from dual.

5 13 im April 2003 next_day(d,day) Datum ist der angegebene Tag der Woche, der dem heutigen Tag folgt select next_day(sysdate,`Tuesday`) from dual; 03-JAN-02 am 30 Dezember 2001 Datumsarithmetiken Wenn Sie zum ersten Mal mit dem Datentyp Date arbeiten, fragen Sie sich folgendes: Vor wie Vielen Tagen wechselten wir bei unserem Auto das l? Mithilfe der Datenarithmetik finden Sie die Antwort. Wenn Sie zu einer Datumsspalte 2 hinzu addieren, wei Oracle , dass es sich um zwei Tage handelt. Date(a) date(a) ist eine Funktion (Extraktion), die f r den Ausdruck a einen Datumswert bildet. Ergebnis Funktion Date(a) a ist Datumswert oder ein alphanumerischer Wert, der dem aktuellen Datumsformat entspricht dieser Datumswert a ist alphanumerischer Wert, der nicht dem aktuellen Datumsformat entspricht Fehlermeldung a ist Zeitstempelwert oder ein alphanumerischer Wert, der dem aktuellen Zeitstempelformat entspricht der Datumswert, der Teil des Zeitstempels ist a ist Festkomma- oder Gleitkommazahl Datumswert, der dem x-ten Tag nach dem entspricht (x=TRUNC(a) ) a ist NULL-Wert NULL-Wert a ist Spezial-NULL-Wert Fehlermeldung NULL-Wert Der Datentyp NULL-Wert (undefinierter Wert)

6 Ist ein spezieller Wert, dessen Vergleich mit einem beliebigen Wert immer undefiniert ist. Frank Nussb cher 3 Tabelle 2 // bliche Formate bei Datumsangaben Format R ckgaben Beispiel Anzeigen Y oder YY oder YYY Die letzte Ziffer, die letzten beiden oder die letzten drei Ziffern der Jahresangabe select to_char(sysdate,`YYY`) from dual; 002 f r alle Datumsangaben in 2002 SYEAR oder YEAR Das Jahr ausgeschrieben das S setzt ein Minuszeichen f r Datumsangaben vor Christi Geburt select to_char(sysdate,(`SYEAR`) from dual; -1112 im Jahr 1112 vor Christus Q Quartal des Jahres (Januar bis M rz=1) select to_char(sysdate,`Q`) from dual; 2 f r alle Datumsangaben im Juni MM Monat (01-12.))

7 Dec=12) select to_char(sysdate,`MM`) from dual; 12 f r alle Datumsangaben im Dezember RM Monatsnummerierung mit r mischen Zahlen select to_char(sysdate,`RM`) from dual; IV f r alle Datumsangaben im April Month Monatsname als neunstelliger Name select to_char(sysdate, Month ) from dual; Mai, gefolgt von 6 Leerstellen f r alle Datumsangaben im Mai WW Woche im Jahr select to_char(sysdate, WW ) from dual; 24 am 2002 W Woche im Monat select to_char(sysdate,`W`) from dual; 1 am 2002 DDD Tag im Jahr: der ist 001, der ist 032, etc. select to_char(sysdate,`DDD`) from dual; 363 am 2002 DD Tag im Monat select to_char(sysdate,`DD`) from dual; 04 am in jedem Jahr D Wochentag (1-7) select to_char(sysdate,`D`) from dual; 5 am rz 2002 DY Abk rzung f r den Tagesnamen select to_char(sysdate,`DY`) from dual; SUN am rz 2002 HH oder HH12 Stunde am Tag (1-12) select to_char(sysdate,`HH`) from dual; 02, wenn es 2 Stunden und 8 Minuten nach Mitternacht ist HH24 Stunde am Tag bei 24-st ndiger Angabe select to_char(sysdate,`HH24`) from dual; 14, wenn es 2 Stunden und 8 Minuten nach Mittag ist MI Minuten (0-59) select to_char(sysdate,`MI`) from dual.

8 17, wenn es 4:17 nachmittags ist SS Sekunden (0-59) select to_char(sysdate,`SS`) from dual; 22, wenn es 11:03:22 ist Nehmen wir einmal an, die sale_date-Spalte enth lt die Angabe 03-MAR-02. In diesem Fall w rde die SQL-Anweisung select sale_date+10 from customer; die Angabe 13-MAR-02 zur ckzuliefern. Sehen wir uns weitere Beispiel zur Datumsarithmetik an. Oracle k mmert sich um Arithmetiken, bei denen die Monats- oder Jahresgrenze berschritten wird. So liefert die Anweisung select to_char(sysdate+14) from dual; f r den 2002 beispielsweise den Wert 06-JAN-03 zur ck. Frank Nussb cher 4 Datums- und Zeitformat Das Datums- und Zeitformat (datetimeformat) spezifiziert die Darstellungsform f r Datums-, Zeit- und Zeitstempelwerte.

9 In SQL-Anweisungen legt es fest, in welcher Darstellung Datums-, Zeit- und Zeitstempelwerte in diesen SQL-Anweisungen enthalten sein k nnen und wie Ergebnisse dargestellt werden sollen. Bei der Syntax-Eingabe kann die Darstellung des Datums bzw. Zeit. In der unteren Tabelle sind die verschiedenen Darstellungsvarianten. Syntax: <datetimeformat> ::= EUR | INTERNAL | ISO | JIS | USA Format Allgemeine Form Beispiel EUR ' ' ' ' INTERNAL 'YYYYMMDD' '20050123' ISO/JIS 'YYYY-MM-DD' '2005-01-23' USA 'MM/DD/YYYY' '01/23/2005' EUR ' ' ' ' INTERNAL 'HHHHMMSS' '00143008' JIS/ISO 'HH:MM:SS' '14:30:08' USA 'HH:MM AM (PM)' '2:30 PM' In allen Zeitformaten muss die Stundenbezeichnung mindestens einstellig sein.

10 Im Zeitformat USA kann die Minutenbezeichnung ganz entfallen, in den anderen Formaten au er INTERNAL muss die Minuten- und Sekundenbezeichnung mindestens einstellig sein. In den Formaten ISO und USA werden die Stunden modulo 24 ausgegeben. In den Formaten EUR und JIS f hren Stundenangaben >99 zu einem Fehler. Im Format INTERNAL k nnen Stundenangaben bis 9999 verwendet werden. In der folgenden Tabelle sehen Sie, welche unterschiedlichen Varianten mit Datum und Zeit angezeigt werden k nnen. Format Allgemeine Form Beispiel EUR/JIS/USA ' ' ' ' ISO 'YYYY-MM-DD HH: ' '2005-01-23 14:30 ' INTERNAL 'YYYYMMDDHHMMSSMMMMMM' '20050123143008456234' Frank Nussb cher 5 Erl uterung Das Argument date_or_timestamp_expression in einer Funktion muss als Ergebnis ein Datum, einen Zeitstempel oder einen alphanumerischen Wert liefern, der dem aktuellen Datums- oder Zeitstempelformat entspricht.