La Conversion de type datetime ne parvient pas uniquement sur la clause where?
Je vais avoir un problème avec certaines requêtes SQL server. S'avère que j'ai une table avec "Attibute_Name" et "Attibute_Value", qui peut être de tout type, stocké dans le type varchar. (Oui... je sais).
Toutes les dates d'un attribut en particulier semblent être stocké le "AAAA-MM-JJ hh:mm:ss" format (pas 100% sûr à ce sujet, il y a des millions d'enregistrements ici), donc je peut exécuter ce code sans problèmes:
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'
Cependant, si j'exécute le code suivant:
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()
J'obtiens l'erreur suivante:
échec de la Conversion lors de la conversion de la date et/ou le temps de chaîne de caractères.
Comment se fait il échoue sur la clause where, et non sur la en choisir un?
Un autre indice:
Si, au lieu de filtrage par le Attribute_Name je utiliser le Attribute_ID stockées dans la base de données (PK), cela fonctionnera sans problème.
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()
mise à Jour
Merci à tous pour les réponses. J'ai trouvé difficile de choisir une bonne réponse parce que tout le monde a montré quelque chose qui est utile à la compréhension de la question. C'est certainement d'avoir à faire à l'ordre de l'exécution.
S'avère que ma première requête a fonctionné correctement car la clause where a été exécuté en premier, puis le SÉLECTIONNER.
Ma deuxième requête a échoué à cause de la même raison (comme les Attributs n'ont pas été filtré, la conversion a échoué lors de l'exécution de la même clause where).
Mon troisième requête a fonctionné parce que l'ID a été le cadre d'un index (PK), donc il a fallu la priorité et il a foré les résultats sur la condition de la première.
Merci!
sOmeDaTe
- dans le cas où vous utilisez un casse classement? Probablement son gâcher vos jointures.Vous semblez être en supposant une sorte de court-circuit de l'évaluation ou de la garantie de commander des prédicats dans la
WHERE
clause. Ce n'est pas garanti. Lorsque vous avez mélangé les types de données dans une colonne comme que la seule façon de les traiter est avec un CASE
expression.Ce tableau est PHA? Si PHA est une table différente de PA il semblerait que la PHA de données unconvertable enregistrements, où que l'autorité palestinienne n'a pas.
Je sais pourquoi vous n'êtes pas sûr à 100% - si vous utilisez un
VARCHAR
colonne pour stocker des valeurs de date/heure, chacun est libre de stocker n'importe quelle vieille ordure là qu'ils veulent. Pour la VAE, j'ai toujours préféré les séparer dans ces colonnes appropriées pour au moins les trois types de base, un attribut de chaîne, un attribut de date/heure, et un attribut numérique. Il n'est pas parfait et ce n'est pas un libre-échange, mais vous avez une bien meilleure chance à assurer des données non valides reste en dehors de votre base de données.Smith, je viens de lire Remus' article et j'ai enfin compris ce que vous avez dit. Vous avez raison, il est tout à fait compréhensible.
OriginalL'auteur Alpha | 2011-08-31
Vous devez vous connecter pour publier un commentaire.
Si la conversion est dans la clause where, il peut être évalué pour beaucoup plus de dossiers (valeurs) qu'elle serait si elle apparaît dans la liste de projection. J'ai parlé de cela auparavant dans un contexte différent, voir T-SQL ne pas s'accompagner d'un certain ordre d'exécution et Sur SQL Server opérateur booléen de court-circuit. Votre cas est encore plus simple, mais est semblable, et, finalement, la cause est la même: ne pas assumer un impératif d'ordre d'exécution lorsque vous traitez avec un langage déclaratif comme SQL.
Votre meilleure solution, par une mesure et une grande marge, est de désinfecter les données et modifier la colonne de type DATETIME ou DATETIME2 type. Tous d'autres solutions de contournement ont une lacune ou d'une autre, de sorte que vous peut-être préférable de faire la bonne chose.
Mise à jour
Après avoir regarder de plus près (désolé, je suis @VLDB et seulement furtivement entre les sessions) je me rends compte que vous avez une VAE magasin avec inhérente type-gratuit sémantique (le
attribute_value
peut bea chaîne, une date, un int, etc). Mon avis est que votre meilleur pari est d'utilisersql_variant
dans le stockage et tout le chemin jusqu'à la client (ie. projetsql_variant
). Vous pouvez coherce le type du client, l'ensemble des Api clientes qui ont des méthodes pour extraire l'intérieur d'unesql_variant
, voir À l'aide de Données sql_variant (enfin, presque toutes les Api client... En utilisant le type de données sql_variant dans le CLR). Avecsql_variant
vous pouvez stocker plusieurs types de w/o les problèmes de passer par les représentations de chaîne, vous pouvez utiliserSQL_VARIANT_PROPERTY
pour inspecter des choses comme laBaseType
dans les valeurs stockées, et vous pouvez même ne pense comme la vérification des contraintes à respecter le type de données correct.SQL_VARIANT
moins vous êtes en train de faire de présentation, de filtrage et de comparaisons au niveau du client. Dans notre EAV système rapidement, nous nous sommes éloignés deSQL_VARIANT
en faveur de l'dédié colonnes pour chaque type. Ok, vous avez donc deux valeurs Null dans chaque ligne, mais vous n'avez pas à traiter avec toutes les vilaines choses qui viennent avec elle. Juste pour donner un traitement équitable sur les deux côtés, je postais un article un peu sur les limitations ici: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/... ... pouvez-vous montrer à votre requête si la colonne ont étéSQL_VARIANT
?Je vois votre point de vue. Faire et globale à l'sql_variant EAV structure de tomber victime de la fonte des questions, tout en un dédié colonne/type peut facilement regrouper les valeurs parce qu'il savent qu'ils sont tous dans le champ type et le type nécessite pas de CAST. Objection valable.
OriginalL'auteur Remus Rusanu
Vous semblez être en supposant une sorte de court-circuit de l'évaluation ou de la garantie de commander des prédicats dans la
WHERE
clause. Ce n'est pas garanti. Lorsque vous avez mélangé les types de données dans une colonne comme que la seule façon de les traiter est avec unCASE
expression.Utiliser (par exemple)
Pas
OriginalL'auteur Martin Smith
Cela a à voir avec l'ordre qu'un
SELECT
requête est traitée. LeWHERE
clause est traitée longtemps avant laSELECT
. Il a pour déterminer les lignes à inclure/exclure. La clause qui utilise le nom doit utiliser une analyse qui examine toutes les lignes, certains ne contiennent pas de date/heure valide les données, tandis que la clé probablement conduit à une recherche et aucune des lignes invalides sont inclus au point. Le convertir dans la liste de sélection est effectuée en dernier, et clairement, cette fois, il ne va pas essayer de convertir les lignes invalides. Puisque vous êtes de mélange de date/heure données avec d'autres données, vous pouvez envisager de stocker la date ou de données numériques dans des colonnes avec des types de données correcte. En attendant, vous pouvez différer le vérifier de la manière suivante:Mais la meilleure solution est probablement d'utiliser le
Attribute_ID
clé à la place du nom si possible.SELECT
liste peut être évalué avant laWHERE
filtre. Voir par exemple cette réponse ou cet élément de connexionNon, cela ne fonctionnera pas. Vous faites l'hypothèse que l'ordre de déclaration (sous-requête) implique ordre de l'évaluation comme dans rusanu.com/2011/08/10/... QO pouvez choisir un plan qui évalue les CONVERTIR avant le attribute_name de comparaison et de déclencher l'erreur de conversion.
Non, j'aurais dit, "vous pouvez essayer de reporter"... la meilleure solution est de stocker les données dans les colonnes du droit type de données, au lieu de se fourrer le tout dans une colonne de type varchar.
que diriez-vous si nous n'avons pas la force DATETIME de conversion. Nous ne devrions pas faire de mauvaises données dans les résultats en raison de la ISDATE() à l'intérieur de la sous-requête, et il est clair que cette colonne n'est pas indexé de toute façon, donc sargeability n'est pas un problème... Après tout, si l'OP n'a pas le pouvoir de faire le schéma parfait, et doit composer avec ce qu'il a?
Le
ISDATE()
être déplacé par QO de la sous-requête et être évalués après le CONVERTIR. QO est libre de le faire. Il serait en effet pas se produire à moins d'une très index spécifique jette un 'piège' trop sucré pour le QO à passer. J'ai vu des produits en cas de support des applications qui a fait quelque chose de similaire et ont été cassés sur la mise à niveau vers la nouvelle version SQL. il n'y a vraiment pas fiables au milieu de la route approche, que je sache, qui impose un système de types fort (projetDATETIME
colonne) sur le dessus de flexibilité de la VAE de stockage comme dans l'OP.sql_variant
est à mon humble avis la correspondance la plus proche (Magasin et projetsql_variant
)OriginalL'auteur Aaron Bertrand
Semble être un problème de données pour moi. Jetez un oeil à des données lorsque vous sélectionnez à l'aide de deux méthodes différentes, essayez de rechercher distinctes lengthsand puis sélectionnez les éléments dans les différentes séries et le globe oculaire. Vérifiez également que les valeurs null? (Je ne suis pas sûr de ce qui se passe si vous essayez de convertir la valeur null à un datetime)
OriginalL'auteur kmcc049
Je pense que le problème est que vous avez un mauvais jour de votre base de données (évidemment).
Dans votre premier exemple, où vous n'êtes pas vérifier la date dans le
WHERE
clause, toutes les dates où un.attribut.Nom = 'SomeDate' sont valables, de sorte qu'il n'essaie de convertir une mauvaise date.Dans votre deuxième exemple, l'ajout à la
WHERE
clause est à l'origine de la requête à l'intention de convertir toutes ces dates et de trouver le mauvais et puis, regardant le nom de l'attribut.Dans votre troisième exemple, la modification de l'utilisation
Attribute_Id
probablement des modifications du plan de requête de sorte qu'il ne regarde que pour ceux where id = 15 d'Abord, puis vérifie pour voir si ces enregistrements ont une date valide, ce qu'ils font. (Peut-êtreAttribute_Id
est indexé etAttribute_name
n'est pas)Donc, vous avez un mauvais jour, quelque part, mais il n'est pas sur tous les registres avec Arttribute_id = 15.
OriginalL'auteur PaulStock
Vous pouvez vérifier les plans d'exécution. Il se peut qu'avec la première requête, le deuxième critère (
CONVERT(DATETIME, pa.Attribute_Value) < GETDATE()
) est évalué en premier sur toutes les lignes, y compris ceux avec des données non valides (pas de date), tandis que dans le cas de la deuxièmea.Attribute_ID = 15
évaluée en premier. Ainsi, en excluant les lignes avec des non-valeurs de date.btw, le second pourrait être plus rapide aussi, et si vous n'avez pas quelque chose de
Attributes
dans la liste de sélection, vous pouvez vous débarrasser deinner join Attributes a on a.Attribute_ID = pa.Attribute_ID
.Sur cette note, il serait souhaitable de se débarrasser de VAE alors qu'il est pas trop tard 🙂
ProductAttributes
contient des millions de lignes, l'évaluation de laCONVERT(DATETIME, pa.Attribute_Value) < GETDATE()
est très inefficace. Essayez:ANALYZE TABLE ProductAttributes; ANALYZE TABLE Attributes;
et de l'exécution de la première requête à nouveau.ANALYZE TABLE
ne sonne pas bon pour SQL Server.OriginalL'auteur nad2000