Sql decimal to Date

[Fuerchau]

Grundsätzlich gilt:
Ist ein Teilergebnis eines Ausdruckes NULL, ist der gesamte Ausdruck NULL.
Nun meldet dir "to_Date" den Fehler, dass kein Datum in dem unteren Ausdruck steckt.
Nun muss also die tiefste Ebene den NULLIF enthalten:
...char(NULLIF(START_D, 0))...
Dies ergibt NULL für die CHAR-Funktion, die nun NULL liefert.
TO_DATE erhält nun ebenso NULL und liefert dann NULL als Ergebnis, so dass nun IFNULL das 2. Element liefern kann.
IFNULL entspricht der COALESCE-Funktion, mit der Ausnahme, dass nur 2 Argumente verwendet werden können.
NULLIF liefert NULL, wenn der Vergleich identisch ist. Ebenso aber auch, wenn ein Argument NULL ist.

Eine Funktion, die das gewünschte Ergebnis nicht bringen kann, wirft eine Exception aus, die den gesamten SQL abbricht. Ein Abfangen so ist nicht möglich.
Mittels NULLIF kann man aber einen Vergleich anstellen, der die Gültigkeit (eingeschränkt) prüfen kann.
NULLIF entspricht:
case when Feld is null then null else feld end
Und somit kann man hier ebenso andere Püfungen in einem Case-Ausdruck durchführen, die im Fehlerfall eben NULL, alternativ aber auch einen Default liefern können:

case when start_d = 0 then 19000101 else start_d end

Du sieht, es gibt viele Wege zum Erfolg.

[tarkusch]

Danke, danke für die ausführliche Erklärung.

[B.Hauser]

Wenn Du allerdings ein anderes ungültiges numerisches Datum (außer 0) hast, hast Du Dich wieder gebissen.

Ich würde eine kleine SQL-Funktion schreiben, die die Konvertierung macht und im Fehler-Fall das Default-Datum ausgibt.
Etwa so:

Create Function YourSchema / NUM2DATE(DATENUM Decimal(8, 0))
   Returns Date
   Language Sql
   Deterministic
   Reads Sql Data
   Called On Null Input
   Begin
      Declare Continue Handler For SQLEXCEPTION
         Return Date('1900-01-01') ;
      Return Date(Digits(DATENUM) concat '000000') ;
   End;

Aufruf ist dann:

Select Num2Date(NumYYYYMMDD)
   From ...

[tarkusch]

Danke für die ausführliche Informationen.

[Spateneder]

Hallo Birgitta,
ich habe gerade diesen für mich sehr hilfreichen Beitrag gefunden.
Ich bin jetzt auf folgendes Problem gestoßen: Wenn ich die Funktion anwende auf eine Spalte, die auch in der Where-Klausel steht, wird meine Abfrage nicht ausgeführt und ich bekomme auf der unteren Nachrichtenebene Datenzuordnungsfehler in der Datei QSQPTABL, Fehlercode 18 (=Datums-, Zeit- oder Zeitmarkenfeld enthält ungültige Daten.
Erklären kann ich mir das nicht. Wenn ich das Feld aus der Where-Klausel rausnehme läuft alles wunderbar. Hast Du eine Idee, woran das liegen könnte?

[Fuerchau]

Dann zeige mal dein SQL.
Ein Problem besteht immer:
Die Ausdrücke der Select-Liste werden zuerst ausgeführt, bevor die Where-Klausel geprüft wird.
Das wurde irgenwann zu V6R1 umgestellt.

Also im Prinzip so:

select * from (
Select ....) x
where ...

Somit musst du bereits im Select-Ausdruck einen gültigen Wert angeben.

Besser wäre dann ein

select f1 f2, calcdate, ...
from (
select * from table
where datum = gültig
) x

[BenderD]

Die Ausdrücke der Select-Liste werden zuerst ausgeführt, bevor die Where-Klausel geprüft wird.
Das wurde irgenwann zu V6R1 umgestellt.

... das sollte mich wundern, das wäre eine Abkehr vom ANSI Standard.

[Spateneder]

Also:

select ARTNIS, num2date(DATVIS)
from ISBNTBL
where ERDAIS = 20210317

funktioniert fehlerfrei. Aber:

select ARTNIS, num2date(DATVIS)
from ISBNTBL
where ERDAIS = 20210317
and DATVIS > 20211231

bringt den Fehler. Und inzwischen habe ich die banale Ursache gefunden: Einige Datensätze enthalten das "Datum" 20401231. Und leider ist hier (V5R4 ?) am 31.12.2039 das Ende der Zeitrechnung.
Das scheint mir im Moment sowieso recht optimistisch.
Vielen Dank für eure Unterstützung! Das hat mich auf die richtige Spur gebracht.

Einen hab ich noch:
date(digits(DATVIS) concat '000000') liefert den 31.12.2040 als Datumswert.

[BenderD]

... das liegt am verwendeten Datumsformat, aber keineswegs an der where Klausel, selbiger ist es doch egal, welchen Schmutz da jemand in die Datei geschrieben hat. Ich kann immer nur den Kopf schütteln, was es alles noch gibt, ein halbes Jahrhundert nach Verdrängung der Lochkarte...

D*B

[Fuerchau]

Wenn du das Datumformat in der "set options"-Klausel auf ISO und auch im RPGLE-Header auf ISO stellst, hast du mit dem Datum keine Schwierigkeit.
Das hat nur was mit deinem SQL/Programm-Datum als *DMY oder *YMD zu tun.
Die Datenbank kann schon lange das ISO-Format.

Dies gilt auch für deine num2date()-Funktion.

[B.Hauser]

Die Datenbank kann schon lange das ISO-Format.
Dies gilt auch für deine num2date()-Funktion.

Das ist so nicht korrekt! Ein Datum wird grundsätzlich im Scaliger Format, einer laufenden Nr., die auf dem 01.01.4713 v.Chr aufsetzt gespeichert (steht so in der SQL Referenz!). In SQL wird das Datums-Format nur dazu verwendet diese Scaliger No lesbar zu machen. Welches Datums-Format verwendet wird hängt von der Umgebung ab. Ein Datum außerhalb des gültigen Bereichs des Datums-Format (z.B. ein Datum vor 1940 bei einem Datums-Format mit 2-stelligem Jahr) wird einfach nicht angezeigt, jedoch korrekt verarbeitet.

RPG arbeitet anders. Zwar wird auch hier die Scaliger No aus der Datei gelesen, aber direkt beim Lesen in eine alphanumerische Darstellung in entsprechenden Format (D- oder H-Bestimmungen) innerhalb des Programms konvertiert. Innerhalb von RPG wird dieses alphanumerische Format verwendet und erst wieder unmittelbar vor dem Zurückschreiben in die Scaliger No konvertiert. Damit kann es in RPG Probleme mit einem Feld-Überlauf geben, wenn ein 4-stelliges Datum in ein 2-stelliges Datum konvertiert werden soll.
(Diese Information stammt übrigens direkt von Barbara Morris!)

Embedded SQL ist nochmal anders. In embedded SQL wird für jede verwendete Host-Variable eine zusätzliche Hilfs-Variable angelegt. Das Datums-Format für die Hilfs-Variablen wird jedoch weder aus der D- noch H-Bestimmung genommen, sondern aus der Compile-Option (Compile Command oder SET OPTION Statement) und der Default für das Datums-Format im Compile-Befehl ist *JOB (meist 2-stelliges Datums-Format!).
... damit kann es in dem Moment, in dem die Host-Variable in die Hilfs-Variable übertragen wird (wir sind ja an dieser Stelle in RPG) zu einem Fehler/Feldüberlauf kommen.

Langer Rede kurzer Sinn, in embedded SQL Programmen, sollte man dafür sorgen, dass ein Format mit 4-stelligem Jahr (ISO, EUR, USA), wobei ISO die beste Option ist, verwendet wird. Also einfach ein SET OPTION Statement mit DATFMT = *ISO in die Quellen einfügen.

Birgitta

[Fuerchau]

Warum musst du immer auf die internen Speicherung in der Tabelle hinweisen?
Die sehe ich noch nicht mal bei DSPPFM. Die interssiert hier auch nicht.
Auch übernimmt RPG hier keinerlei Umwandlung vor, das macht bereits die unterste Schicht der DB-Funktionen (noch vor SQL).
DSPPFM habe ich schon erwähnt, da kommt das angegebene Datumformat.
Beim Öffnen einer Tabelle/PF als intern beschriebene Datei bekomme ich ebenso direkt das Datum im lesbaren Format.

In SQL oder RPG arbeitet man immer mit einem Datumfeld im angegebene Format (Set option datfmt=xxx), das Format sieht man dann auch in den Umwandlungslisten.
Das selbe gilt für RPGLE, da wird das Datumformat im Header angegeben.

Somit gibts aus Programmierersicht keinen Unterschied zwischen RPG und SQL.
Man muss halt nur nur das korrekt Datumformat anwenden und dies sollte halt nicht unterschiedlich sein. Man kann im RPGLE mit *ISO arbeiten und in der DSPF/PRTF dann mit *EUR. Hier wandelt die Runtime tatsächlich automatisch um.

Im RPGLE kann ich das Format auch explizit bei einem Datumfeld angeben, was der SQL-Precompiler nämlich auch tut. Das sieht man dann bei den entsprechenden SQLnnnn-Feldern.

Definiert man *YMD in den SQL-Optionen, gibts einen SQL-Fehler (SQLCODE, NULL-Anzeiger).
Nimmt man in SQL *ISO und im RPGLE *YMD, gibts einen RPG-Runtimefehler beim
EVAL HOSTDATUM = SQL4711.

Auch bei Zugriffen via ODBC/JDBC bekomme ich ein Feld vom Typ DateTime, dass immer ein Datum enthält (oder NULL).

Und was anderes, nur kürzer, habe ich oben ja auch nicht gesagt.