SQL Date Between möchte einfach nicht gelingen

[dholtmann]

Hallo zusammen,

ich versuche alle Daten eines bestimmten Bereichs per SQL zu lesen.

Meine Idee war:

select * from Table1 where Table1Datum between date('2016-07-01')
and date('2016-07-30')

das funktioniert leider jedoch nicht: Auswahlfehler für Feld *N.

select * from Table1 where Table1Datum >= date('2016-07-01')

funktioniert, jedoch

select * from Table1 where Table1Datum >= date('2016-07-01') and
Table1Datum >= date('2016-07-30')

funktioniert wieder nicht.
Nicht einmal das Datum vorher als String casten schafft Abhilfe.
Kann mir jemand weiterhelfen?​

[Fuerchau]

Dann prüfe mal genau die Definition deines Table1Datum ob es wirklich vom Typ DATE ist.
Normalerweise benötigt SQL kein manuelles casten wenn das Feld tatsächlich ein Date ist und die Zeichenketten korrekt formatiert sind.

[DKSPROFI]

Moin

wenn in deiner Datei Datumsfelder sind dann so:

select * from Datei
where Feld Between '2016-10-01' and '2016-10-01'

oder, wenn deine Felder rein Numerisch sind, dann

select * from Datei
where Feld Between 20161001 and 20161001

So geht das bei mir.

mfg

DKSPROFI

[dholtmann]

Danke für die Antworten!

Also Date ist es definitiv, denn will ich den Wert in Numeric casten bekomme ich den Fehler:
CAST von DATE in NUMERIC nicht unterstützt.

Vllt ist noch wichtig zu erwähnen, dass ich den select auf eine View anwende, in der aus drei Einzelfeldern ein Date erzeugt wird?
Jedoch sind die Ergebnisse alle korrekte Datumsangaben.
Um DKSPROFIs Tip zu testen habe ich auch mal über Umwege in numeric gecastet.
Selbst da:

where cast(substr(cast( Table1Datum as char(10)), 1, 2) concat
substr(cast( Table1Datum as char(10)), 4, 2) concat
substr(cast( Table1Datum as char(10)), 7, 2) as Numeric(6))
between 010716 and 300716

bringt er mit den Auswahlfehler für Feld *N.

[Fuerchau]

Das kann dann in der View begründet sein.
Schau mal ins Joblog, denn SQL ist da ggf. so gesprächig um mitzuteilen, aufwelchem Satz der Fehler denn passiert.
Ggf. versucht die View aus ungültigen Werten ein Datum zu zaubern, dass dann fehlerhaft ist.
Hierzu sei noch gesagt, dass der Optimizer ggf. gar nicht die View verwendet sondern wieder mal selber den SQL aus dem View-SQL und deinem SQL umbaut.
Der Auswahlfehler kann sich auch auf Auswahlen in der View beziehen und hat mit deinem Datum gar nichts zu tun.
Ich habe z.B. eine View mit einer Where-Klausel definiert. Die Whereklausel schränkt auf einen Mandanten ein. Mache ich nun einen "Select * from MyView where xx='yy'" wird kein Index verwendet obwohl es für die Where-Klausel der View einen Index gibt, was wiederum für irgendeinen Umbau spricht.
Sollten also in der View irgendwelche anderen Casts laufen oder ggf. ungültige Vergleiche stattfinden, führt dies zu deinem Fehler.

[dholtmann]

Stimmt, Satz 10 sei fehlerhaft!
Dankeschön!
Kann es helfen, in der View dann per where alle Sätze auszuschließen in denen das Datum ungültig ist bzw in den Fällen ein Standard Datum zu vergeben?

[BenderD]

... das Problem ist, dass der Query Optimizer, je nach Ausführungsplan, die Ausführungsreihenfolge ändert und dann über diese Sätze fällt. Gegenwärtig kriegt man das noch weg, wenn man die Datums-Verdreherei in eine Function auslagert und da den Ersatzwert zurückgibt - in die Function guckt die Query Engine nicht rein. Da gibt es, soweit ich das erinnere eine universelle Datumsfunction auf think400.dk, die alles mögliche kann.

D*B

[B.Hauser]

Ich würde eine UDF (User Defined Function) definieren und diese in die View einbinden, anstatt zu versuchen das Datum manuell zu konvertieren.
In der UDF kann ein ungültiges Datum durch einen Continue Handler abgefangen werden und statt des falschen Werts ein Dummy Datum (z.B. 01.01.0001 oder 31.12.9999 oder was auch immer) ausgegeben werden.
UDFs können in SQL wie skalare Funktionen eingesetzt werden.
(Es soll übrigens auch Firmen geben, die SQL und SQL-Programmierung schulen)

Die folgende UDFs empfängt ein numerisches Datum im Format JJJJMMTT und konvertiert dieses in ein echtes Datum.
Im Fehlerfall, also bei einem ungültigen Datum wird der 01.01.0001 ausgegeben.

CREATE OR REPLACE FUNCTION YourSchema.CVTNUMTODATE 
                  (PARDATENUM DECIMAL(8, 0)) 
	RETURNS DATE   
	LANGUAGE SQLDETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
        RETURN DATE ( '0001-01-01' ) ; 
  
RETURN DATE ( DIGITS ( PARDATENUM ) CONCAT '000000' ) ; 
END  ;

Birgitta

[Fuerchau]

Die Frage ist ggf. ob das Datum erkennbar falsch ist (z.B. 0 oder 99999999).
In diesem Fall kann man das auch mit einem CASE-Ausdruck lösen und den Default oder NULL zurückgeben.

Je nach dem wie die 3 Felder definiert sind hilft ggf. auch einfach dieses:
cast('0001-01-01') as date + (JJFeld -1) years + (MMFeld - 1) months + (TTFeld - 1) days

Wenn das JJFeld 2-stellig ist, kannst du das mit einem CASE wieder geradebiegen bzw. das Startdatum anpassen. Dann gibts auch mit dem Optimizer kein Problem.

Bei der Performance mit der UDF ist das nämlich so eine Sache, insbesonders wenn man auf die UDF noch eine Whereklausel hat, da zieht nämlich kein Index.

[BenderD]

... wenn's um Performance geht, hilft bei diskreten Werten eine Umsetztabelle, wenn das ganze in einem Programm passiert, ist es oft vorteilhaft Selektion per Extrakte (temp tables) vorzuziehen...
D*B

[Fuerchau]

Ich weiß auch nicht, ab wann der Optimizer seine Verfahren geändert hat (ich meine aber erst ab V6R1), dass Berechnungen der Select-Felder vor der Where-Klausel durchgeführt werden.
Mir ist es nämlich passiert, dass SQL's von V5 nach V6 nicht mehr funktionierten.
Per Where-Klausel hatte ich fehlerhafte Daten ausgefiltert um dann die korrekten Daten per CAST o.ä. anzupassen. Dies funktionierte nicht mehr, da der CAST häufig, aber nicht immer, vor dem Where ausgeführt wird.
Dies führt eben dazu, dass man verstärkt in seinen Berechnungen nun auch die Prüfungen der Where-Klausel wiederholen muss.
Aber auch hier ist die Reihenfolge der Ausführung eines Ausdruckes nicht gewährleistet, so dass es trotzdem zu Cast-Fehlern kommt obwohl man sie doch eigentlich per Case-Ausdruck ausgeschlossen hat. Hier hilft dann wirklich nur noch eine UDF.

Bei komplexen Ausdrücken hilft manchmal die NULL-Regel:
Ist ein Teilausdruck NULL wird der Gesamtausdruck NULL.

Beispiel:
coalesce(substr(MyFeld, 1, nullif(position('-' in MyFeld) - 1, -1)), MyFeld)

[BenderD]

... bei den Huddeldatumsfeldern (und um die geht es wie hier meist) verstehe ich die ganze Aufregung nicht. Eine einzige Umsetztabelle mit 36.500 Sätzen deckt 100 Jahre ab, was wohl den meisten reichen würde und von der Satzanzahl ist das für heutige AS/400 so gut wie nix. Da habe ich dann als Primary key ein Feld vom Typ Date und für jedes Huddelformat ein Feld mit korrespondierendem Wert. Damit da nicht nur Huddel drinsteht, kann ich gleich noch Felder für Vormonat, Ultiomo etc. mit aufnehmen. Habe ich Huddel und brauche ein ordentliches Format, wird die Tabelle gejoined - et voila...

D*B