SQL Server DateTime l'échec de conversion

J'ai une grande table avec 1 million de+ enregistrements. Malheureusement, la personne qui a créé la table a décidé de mettre les dattes dans un varchar(50) champ.

J'ai besoin de faire une simple comparaison de dates -

datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

Mais il échoue sur la convert():

Conversion failed when converting datetime from character string.

Apparemment, il y a quelque chose dans ce domaine, il n'aime pas, et comme il ya tellement de nombreux dossiers, je ne peux pas dire tout simplement en le regardant. Comment apprendre à bien désinfecter l'ensemble du champ de date afin de ne pas échouer sur la convert()? Voici ce que j'ai maintenant:

select count(*)
from MyTable
where
    isdate(lastUpdate) > 0
    and datediff(dd, convert(datetime, lastUpdate, 100), getDate()) < 31

@SQLMenace

Je ne suis pas préoccupé par la performance dans ce cas. Cela va être un moment de la requête. Modification d'une table à un champ datetime n'est pas une option.

@Jon Limjap

J'ai essayé d'ajouter le troisième argument, et il ne fait aucune différence.

@SQLMenace

Le problème est probablement la façon dont les données sont stockées, il y a seulement deux sûre formats; ISO AAAAMMJJ; ISO 8601 aaaa-mm-jj Thh:mm:ss:mmm (sans les espaces)

Ne serait pas la isdate() vérification de prendre soin de cela?

Je n'ai pas besoin d'une précision de 100%. Je veux juste que la plupart des enregistrements à partir des 30 derniers jours.

@SQLMenace

select isdate('20080131') -- returns 1
select isdate('01312008') -- returns 0

@Brian Schkerke

Place le CAS et ISDATE à l'intérieur de la fonction CONVERT() fonction.

Merci! Qu'il a fait.

Vous n'avez pas mentionné ce que les formats les dates sont dans le champ de type varchar. Sont-elles régulièrement mises en forme? Sachant que le format de l'aider un peu avec la résolution du problème.

OriginalL'auteur Seibar | 2008-08-26

datetime sql-server

Lieu le CASE et ISDATE à l'intérieur de la CONVERT() fonction.

SELECT COUNT(*) FROM MyTable
WHERE
    DATEDIFF(dd, CONVERT(DATETIME, CASE IsDate(lastUpdate)
        WHEN 1 THEN lastUpdate
        ELSE '12-30-1899'
    END), GetDate()) < 31

Remplacer '12-30-1899' avec la date par défaut de votre choix.

OriginalL'auteur Skerkles

3

Comment de l'écriture d'un curseur pour parcourir le contenu, de tenter de la lancer pour chaque entrée?Lorsqu'une erreur se produit, la sortie de la clé primaire ou d'autres données d'identification pour l'enregistrement de problème.
Je ne peux pas penser à une base de jeu moyen pour ce faire.

Pas totalement setbased mais si seulement 3 lignes de 1 million de dollars sont mauvais, il va vous faire économiser beaucoup de temps
```
select * into BadDates
from Yourtable
where isdate(lastUpdate) = 0

select * into GoodDates
from Yourtable
where isdate(lastUpdate) = 1
```
ensuite, il suffit de regarder la BadDates table et fixer que

OriginalL'auteur SQLMenace
2

Le ISDATE() pour s'occuper des lignes qui n'ont pas été correctement formaté si elle était en effet d'être exécuté en premier. Cependant, si vous regardez le plan d'exécution, vous trouverez probablement que les DATEDIFF prédicat est appliqué en premier - c'est donc la cause de votre douleur.

Si vous êtes à l'aide de SQL Server Management Studio appuyez sur CTRL+L pour afficher l'estimation du plan d'exécution pour une requête particulière.

Rappelez-vous, SQL n'est pas une langue de la procédure et de court-circuiter la logique de travail, mais seulement si vous êtes prudent dans la façon dont vous l'appliquez.

OriginalL'auteur Skerkles
1

Comment de l'écriture d'un curseur pour parcourir le contenu, de tenter de la lancer pour chaque entrée?

Lorsqu'une erreur se produit, la sortie de la clé primaire ou d'autres données d'identification pour l'enregistrement de problème.

Je ne peux pas penser à une base de jeu moyen pour ce faire.

Modifier - ah oui, j'ai oublié ISDATE(). Certainement une meilleure approche à l'aide d'un curseur. +1 pour SQLMenace.

OriginalL'auteur Ian Nelson
0

Dans votre convertir appel, vous devez spécifier un troisième paramètre de style, par exemple, le format de la datetimes qui sont stockés en tant que varchar, comme spécifié dans ce document: CAST et CONVERT (T-SQL)

OriginalL'auteur Jon Limjap
0

D'imprimer les documents. Donner le papier à l'idiot qui a décidé d'utiliser un varchar(50) et demandez-leur de trouver l'enregistrement de problème.

La prochaine fois, ils peuvent tout simplement voir le point de choisir un type de données approprié.

OriginalL'auteur Ian Nelson
0

Le problème est probablement la façon dont les données sont stockées, il y a seulement deux sûre formats

ISO AAAAMMJJ

ISO 8601 aaaa-mm-jj Thh:mm:ss:mmm(sans les espaces)

ces fonctionne, peu importe ce que votre langue est.

Vous pourriez avoir besoin pour faire un SET DATEFORMAT YMD (ou de ce que les données sont stockées sous) pour le faire fonctionner

OriginalL'auteur SQLMenace
0

Ne serait pas le isdate() case à prendre soin de cela?

Le lancer pour voir ce qui se passe
```
select isdate('20080131')
select isdate('01312008')
```
OriginalL'auteur SQLMenace
0

Je suis sûr que la modification de la table/colonne peut ne pas être une option en raison de tout l'héritage du système, mais avez-vous pensé à la création d'un point de vue qui a la date de conversion de la logique construit à, si vous utilisez une version plus récente de sql, alors vous pouvez peut-être même utiliser une vue indexée?

OriginalL'auteur chris raethke
0

Je suggère de nettoyage de la pagaille et de la modification de la colonne datetime, parce que faire des trucs comme ça
```
WHERE datediff(dd, convert(datetime, lastUpdate), getDate()) < 31
```
ne peut pas utiliser un index, et il sera plusieurs fois plus lent que si vous aviez une colonne datetime,de n et de ne
```
where lastUpdate > getDate() -31
```
Vous devez également prendre en compte les heures et les secondes de cours

OriginalL'auteur SQLMenace

Vous devez vous connecter pour publier un commentaire.