Umwandlung in datetime scheitert nur auf die WHERE-Klausel?

Ich habe ein problem mit einigen SQL-server Abfragen. Stellt sich heraus, dass ich eine Tabelle mit "Attibute_Name" und "Attibute_Value" Felder, die beliebigen Typs sein können, gespeichert in varchar. (Ja... ich weiß.)

Alle Termine für ein bestimmtes Attribut zu sein scheinen gespeichert, die die "YYYY-MM-DD hh:mm:ss" format (nicht 100% sicher, es gibt Millionen von Einträgen hier), so kann ich diesen code ausführen, ohne Probleme:

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'

Allerdings, wenn ich führen Sie den folgenden code:

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_Name = 'SomeDate'
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

Ich die folgende Fehlermeldung erhalten:
Fehler bei der Konvertierung beim konvertieren von Datum und/oder Zeit von-Zeichenfolge.

Wie kommt es scheitert an der where-Klausel und nicht auf die wählen Sie eine?

Weiteren Hinweis:

Wenn statt der Filterung durch die Attribute_Name ich mit der eigentlichen Attribute_ID in der Datenbank gespeichert (PK) es funktioniert ohne problem.

select /*...*/ CONVERT(DATETIME, pa.Attribute_Value)
from 
    ProductAttributes pa
    inner join Attributes a on a.Attribute_ID = pa.Attribute_ID
where 
    a.Attribute_ID = 15
    and CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()

Update
Danke an alle für die Antworten. Ich fand es schwer, um tatsächlich wählen Sie eine richtige Antwort, weil jeder darauf hingewiesen, etwas, das nützlich für das Verständnis, die Frage. Es war auf jeden Fall mit, um mit der Reihenfolge der Ausführung.
Stellt sich heraus, dass meine erste Abfrage korrekt funktionierte, weil die WHERE-Klausel war zuerst ausgeführt, dann die WÄHLEN Sie.
Meine zweite Abfrage ist fehlgeschlagen, weil der Grund derselben (wie die Attribute wurden nicht gefiltert, die Konvertierung ist fehlgeschlagen, während der Ausführung der gleichen WHERE-Klausel).
Meine Dritte Abfrage funktioniert, weil die ID war Teil der index (PK), so dass es den Vorrang hatten und es aufgerissen Ergebnisse auf, dass die Bedingung zuerst.

Dank!

  • gibt es ein weiteres Attribut, genannt sOmeDaTe - falls Sie eine case insensitive Sortierung? Wahrscheinlich seine vermasselt Ihr verbindet.
  • Du scheinst vorausgesetzt werden, irgendeine Art von Kurzschluss-Auswertung oder garantierte Reihenfolge der Prädikate in der WHERE - Klausel. Ist dies nicht gewährleistet. Wenn Sie über gemischte Datentypen in einer Spalte, so ist die einzige sichere Umgang Sie ist mit einem CASE Ausdruck.
  • Was für ein Tisch ist PHA? Wenn ein PHA ist, eine andere Tabelle als die PA, dann scheint es, dass PHA Daten hat einige unconvertable records, wo, wie PA ' s nicht.
  • Ich weiß, warum du bist nicht 100% sicher - wenn Sie einen VARCHAR Spalte zum speichern von Datum/Zeit-Werte, jedem steht es frei, zu speichern, alten Kram gibt, dass Sie wollen. Für die EAV habe ich immer bevorzugt die Trennung von dieser aus in die richtigen Spalten für mindestens die drei Basis-Typen, ein string-Attribut, die ein Datum/Uhrzeit-Attribut und einem numerischen Attribut. Es ist nicht perfekt und ist es nicht eine free-trade-off, aber Sie haben eine viel bessere Aufnahme zu gewährleisten, ungültige Daten immer aus der Datenbank.
  • Gerade überprüft, die Spalte Sortierung SQL_Latin1_General_CP1_CI_AS also ich denke, es würde der Fall sein, aber ich fand keine anderen anders-cased Wert. Gut Schießen!
  • Smith, Nicht wirklich, ich bin eigentlich abstrahieren mich aus diesem Standpunkt, so würde ich erwarten, dass es funktioniert oder nicht, egal wo die Vergleiche gemacht werden. Das ist, was motiviert meine Frage.
  • Sorry. Ich bin absichtlich verschleiert die Reale Struktur der DB hat zwei Gründe: 1. halten Sie es einfach, um zu zeigen, das problem ich habe 2. vertrauliche Informationen nicht offenlegen, dass ich rechtlich nicht erlaubt. Ich schon fest, dass in den Abfragen vor.
  • Bertrand ich Stimme zu, und ich weiß, dass es einige nicht-Datums-Werte in dieser Spalte, aber ich bin einrichten der Filter, was soll nur datetime (und die Tatsache, dass eine der Abfragen nicht zu Versagen sollten bestätigen, dass Sie alle korrekt konvertiert datetime). Dies ist nicht so beschissen wie die Werte kommen aus einem automatischen system, so kann ich erwarten, dass Sie, um gültig zu sein... aber ich bin mir des Risikos bewusst.
  • Nicht sicher, warum Sie sagen, Sie sind abstrahiert aus, dass POV. Attribute_Value enthält nicht die Termine, die Sie Gießen Sie die Spalte ein Datum. Sie müssen glauben, daß eine form der vorab-Filterung ist gewährleistet.
  • Smith, den ich gerade gelesen Remus' Artikel, und ich endlich verstanden, was Sie sagte. Du hast Recht, es macht Total Sinn.

InformationsquelleAutor Alpha | 2011-08-31
  1. 2

    Wenn die Umwandlung in der WHERE-Klausel kann es bewertet werden, für viele weitere Einträge (Werte), als es wäre, wenn es erscheint in der projektionsliste. Ich habe darüber gesprochen, bevor Sie in einem anderen Kontext, siehe T-SQL-Funktionen nicht zu unterstellen, dass eine bestimmte Reihenfolge der Ausführung und Auf SQL Server booleschen operator Kurzschluss. Dein Fall ist sogar noch einfacher, aber ähnlich ist, und letztendlich die Ursache ist die gleiche: Sie nicht davon ausgehen, dass ein Imperativ Ausführung, um beim Umgang mit einer deklarativen Sprache wie SQL.

    Ihre beste Lösung, durch eine weit-und eine große Spanne, ist zu bereinigen, die Daten und ändern Sie die Spalte Typ in einen DATETIME-oder DATETIME2-Typ um. Alle anderen Problemumgehungen wird ein Mangel oder eine andere, so dass Sie möglicherweise besser, nur das richtige zu tun.

    Update

    Nach einem genaueren Blick (sorry, ich bin @VLDB und nur verstecken, ALSO zwischen den Sitzungen) merke ich, Sie haben eine EAV-Shop mit eigen-Art-kostenloses Semantik (die attribute_value kann bea string, date, int, etc). Meine Meinung ist, dass Ihre beste Wette ist zu verwenden sql_variant im Speicher und den ganzen Weg bis zu dem client (ie. Projekt sql_variant). Sie können coherce der Typ in der client alle client-APIs verfügen über Methoden zum extrahieren der inneren Typ aus einer sql_variant finden Sie Mit sql_variant-Daten (naja, fast alle client-APIs... Mit der sql_variant-Datentyp in CLR). Mit sql_variant können Sie speichern mehrere Typen w/o die Probleme der Umweg über einen string-Darstellungen, die Sie verwenden können, SQL_VARIANT_PROPERTY zu kontrollieren Dinge wie die BaseType in der gespeicherten Werte, und man kann sogar denkt, wie check-Einschränkungen erzwingen-Daten-Typ-Korrektheit.

    • Ich wäre sehr zögerlich zu verwenden SQL_VARIANT es sei denn du tust alle Präsentation, die Filterung und die Vergleiche auf dem client. In unserer EAV-system, das wir schnell bewegte sich Weg von SQL_VARIANT zu Gunsten von engagierten Spalten für jeden Typ. Ok, so haben Sie zwei Nullen in jeder Zeile, aber Sie nicht haben, um sich mit all den anderen fiesen Dinge, die mit ihm gekommen. Nur zu geben, eine faire Chance auf beiden Seiten, die ich gebloggt habe, ein bisschen über die Einschränkungen hier: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/... ... können Sie Ihre Abfrage, ob die Spalte SQL_VARIANT?
    • Ich sehe Ihren Punkt. Tun, und aggregieren über die sql_variant-EAV-Struktur würde Opfer fallen, um die cast-Probleme, während einer dedizierten Spalte/Typ kann leicht aggregieren der Werte, weil Sie wissen, Sie sind alle in dem Feld Typ, und die Art erfordert keine STIMMEN. Gültiger Einwand.
    InformationsquelleAutor Remus Rusanu
  2. 9

    Du scheinst vorausgesetzt werden, irgendeine Art von Kurzschluss-Auswertung oder garantierte Reihenfolge der Prädikate in der WHERE - Klausel. Ist dies nicht gewährleistet. Wenn Sie über gemischte Datentypen in einer Spalte, so ist die einzige sichere Umgang Sie ist mit einem CASE Ausdruck.

    Verwenden (z.B.)

    CONVERT(DATETIME, 
      CASE WHEN ISDATE(pa.Attribute_Value) = 1 THEN pa.Attribute_Value END)

    Nicht

    CONVERT(DATETIME, pa.Attribute_Value)
    InformationsquelleAutor Martin Smith
  3. 1

    Dies hat zu tun mit der Bestellung, dass ein SELECT Abfrage verarbeitet. Die WHERE - Klausel verarbeitet, lange bevor die SELECT. Es hat zu bestimmen, welche Zeilen ein - /ausschließen. Die Klausel, dass der name verwendet wird, muss ein scan untersucht alle Zeilen, von denen einige enthalten keine gültige Datums - /Uhrzeit-Daten, in der Erwägung, dass der Schlüssel wahrscheinlich führt zu einem suchen und keiner der ungültigen Zeilen sind auf den Punkt. Das konvertieren in der Auswahlliste durchgeführt wird, letzten, und, klar, um diese Zeit ist es auch nicht versuchen zu konvertieren ungültige Zeilen. Da Sie die Vermischung von Datum/Zeit-Daten mit anderen Daten, die Sie eventuell für die Speicherung von Datums-oder numerische Daten in dedizierten Spalten mit den richtigen Datentypen enthalten. In der Zwischenzeit können Sie stellen Sie den check-in der folgenden Weise:

    SELECT /* ... */
FROM
(
  SELECT /* ... */
    FROM ProductAttributes AS pa
    INNER JOIN dbo.Attributes AS a
    ON a.Attribute_ID = pa.Attribute_ID
    WHERE a.Attribute_Name = 'SomeDate'
    AND ISDATE (pa.Attribute_Value) = 1
) AS z
WHERE CONVERT(CHAR(8), AttributeValue, 112) < CONVERT(CHAR(8), GETDATE(), 112);

    Aber die bessere Antwort ist wahrscheinlich zu verwenden, die Attribute_ID - Taste anstelle der Namen, wenn möglich.

    • Dies ist nicht garantiert, um zu arbeiten. Die compute scalar-in der SELECT Liste ausgewertet werden können, bevor die WHERE filter. Siehe zum Beispiel diese Antwort oder mit diesem verbinden-Element
    • Nein, das funktioniert nicht. Sie machen die Annahme, dass die Reihenfolge der Deklaration (Unterabfrage) impliziert die Reihenfolge der Auswertung wie in rusanu.com/2011/08/10/... QO können wählen, einen plan, der wertet das KONVERTIEREN bevor der Attributname Vergleich und auslösen der Konvertierung Fehler.
    • Nein, ich sollte gesagt haben: "Sie können versuchen, zu verschieben"... die bessere Antwort ist, zum speichern der Daten in dedizierten Spalten des richtigen Datentyps, statt der Füllung alles in eine varchar-Spalte.
    • wie wäre es, wenn wir nicht die Kraft DATETIME Konvertierung. Wir sollten nicht irgendwelche schlechten Daten in den Ergebnissen, weil der ISDATE() in der Unterabfrage, und es ist klar, dass dies die Spalte nicht indiziert ist sowieso, so sargeability ist nicht ein Problem,... Nach allem, was wenn die OP nicht die macht haben, die das schema perfekt und zu tun hat mit dem, was er hat?
    • Die ISDATE() kann bewegt werden, die von QO aus der Unterabfrage und ausgewertet werden nachdem zu KONVERTIEREN. QO frei ist, so zu tun. Es würde ja nicht passieren, es sei denn, einer sehr spezifischen index legt eine 'Falle' zu süß für die QO vorbei. Ich habe gesehen, echte Produkt-support-Anfragen von apps, die haben etwas ähnliches und waren gebrochen upgrade auf die neue SQL-version. es ist wirklich nicht zuverlässig middle-of-the-road-Ansatz, den ich kenne auferlegt, die ein starkes Typsystem (Projekt DATETIME Spalte) auf der Oberseite einer flexiblen EAV Speicher wie in OP. sql_variant ist imho die beste übereinstimmung (Speicher-und Projekt -sql_variant)
    • Aber who cares, wo die ISDATE() passiert? Es ist nicht mehr jede DATETIME Konvertierung passiert, also es wird nicht länger ein Fehler - im schlimmsten Fall führt dies zu Zeilen gefiltert, bei einer weniger optimalen spot. Wir sind uns alle einig, dass die beste Lösung wäre, um die Daten zu ändern geben, aber das ist nicht die einzige Antwort, und es ist nicht unbedingt eine akzeptable Antwort an alle.
    • Ja, wenn die Konvertierung entfernt (nicht mehr stark typisierte Projektion), dann werden alle Probleme im Zusammenhang mit der unrechtmäßigen BESETZUNG verschwinden.

    InformationsquelleAutor Aaron Bertrand
  4. 0

    Scheint, wie ein Daten-Problem für mich. Werfen Sie einen Blick auf die Daten, wenn Sie wählen Sie es mit den zwei verschiedenen Methoden, versuchen, suchen für verschiedene lengthsand wählen Sie dann die Elemente in den verschiedenen sets und Augapfel Sie. Überprüfen Sie auch für null-Werte? (Ich bin mir nicht sicher, was passiert, wenn Sie versuchen, die Umwandlung von null in einen datetime -)

    • Umwandlung von null in einen datetime-Ergebnisse in eine null.
    InformationsquelleAutor kmcc049
  5. 0

    Ich denke das problem ist, Sie haben ein schlechtes Datum in der Datenbank (offensichtlich).

    In deinem ersten Beispiel, wo Sie nicht überprüfen Sie das Datum in der WHERE Klausel, alle Termine wo einer.Attribut.Name = 'SomeDate' gelten, so dass es nie versucht zu konvertieren von einem schlechten Tag.

    In Ihrem zweiten Beispiel, das zusätzlich zu den WHERE Klausel ist, die den Abfrageplan, um tatsächlich konvertieren Sie alle diese Termine und die Suche nach der bösen betrachtet und dann das Attribut name.

    In deinem Dritten Beispiel, das ändern zu verwenden Attribute_Id wahrscheinlich ändert sich die Abfrage-plan, so dass es sieht nur für diejenigen, wo die id = 15 Zuerst, und dann überprüft, um zu sehen, wenn die Datensätze über ein gültiges Datum, was Sie tun. (Vielleicht Attribute_Id indiziert ist und Attribute_name nicht)

    Also, Sie haben einen schlechten Tag irgendwo, aber es ist nicht auf alle Datensätze mit Arttribute_id = 15.

    InformationsquelleAutor PaulStock
  6. 0

    Können Sie prüfen Ausführungspläne. Es könnte sein, dass mit der ersten Abfrage das zweite Kriterium ( CONVERT(DATETIME, pa.Attribute_Value) < GETDATE() ) wird zuerst ausgewertet über alle Zeilen einschließlich jene, die mit ungültigen Daten (nicht Datum), während im Fall der zweiten - a.Attribute_ID = 15 bekommen, werden zuerst bewertet. Also ohne Zeilen mit nicht-Datums-Werte.

    btw, das zweite könnte man auch schneller, und wenn Sie haben noch nichts von Attributes in der select-Liste, die Sie loswerden können inner join Attributes a on a.Attribute_ID = pa.Attribute_ID.

    In diesem Sinne wäre es ratsam, um loszuwerden, EAV, während es ist nicht zu spät 🙂

    • Sie können versuchen, neu zu berechnen-Tabelle-Statistiken. Wenn ProductAttributes enthält Millionen von Zeilen, die Auswertung der CONVERT(DATETIME, pa.Attribute_Value) < GETDATE() ist höchst ineffizient. Versuchen: ANALYZE TABLE ProductAttributes; ANALYZE TABLE Attributes; und ausführen der ersten Abfrage wieder.
    • ANALYZE TABLE nicht sound Recht für SQL Server.
    InformationsquelleAutor nad2000
Schreibe einen Kommentar