Erreur conversion d'un type de données varchar en type de données datetime

**olibara** · 29/01/2012, 14h36

Bonjour voici une nouvelle facétie de SQL Serveur version Française

Je viens de chercher pendant un bon bout de temps pourquoi une requete qui fonctionne tres bien chez moi me donne un message d'erreur chez un utilisateur en France

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT 
*
FROM 
Stock
WHERE DateIN >= '2012-01-18'

Envoyé par ErrorMessage

Msg*242, Niveau*16, État*3, Ligne*2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Et je viens de découvrir l'HORREUR !
La requete sur Date est dépendante du type d'installation du serveur !!

Le serveur de l'utilisateur est installé en Français !

Il faut alors faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE DateIN >= '18-01-2012'

et non

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE DateIN >= '2012-01-18'

Je pense et j'espère qu'avec une requete parametrée je devrais m'en sortir
Mais je trouve cependant cela horrible !!

J'ose a peine imaginer ce que doit donner un Back-Up restore entre les deux serveurs dans ce cas !!

Vos remarque sur mon interpretation de ce problème sont les bienvenues

**Ptit_Dje** · 29/01/2012, 18h50

Bonjour,

Le format de la date dépend en effet de la langue utilisée pour l'installation.

Pour voir la liste des différents formats par langue installée:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_helplanguage

Pour le francais:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sp_helplanguage 'French'

Pour modifier le comportement de la session, vous pouvez utiliser la commande suivante par exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SET DATEFORMAT ymd

Pour plus d'information, cette commande et le comportement que vous décrivez sont expliqués dans la MSDN

**SQLpro** · 30/01/2012, 10h58

Envoyé par olibara

Bonjour voici une nouvelle facétie de SQL Serveur version Française

Il ne s'agit pas d'une facétie, mais d'un comportement attendu et normatif.

Le format normatif de conversion de STRING en DATETIME dans SQL Server repose sur la norme SQL qui imposait le format 'AAAAMMJJ hh:mm:ss.nnn'
Ceci jusqu'à la norme SQL1999.

Quelque soit les règles du serveur, ce format passe outre le paramétrage de langue.

A partir de SQL Server 2008, qui a rajouté les type DATE, DATETIME2 et DATETIMEOFSSET, le format normatif ayant évolué, SQL Server prends désormais 'AAAA-MM-JJ ...' UNIQUEMENT pour les nouveaux types (afin de rester rétrocompatible).

Si vous aviez cherché un peu, vous seriez tombé sur l'article que nous avons écrit à ce sujet :
http://baptiste-wicht.developpez.com...-sql/datetime/

Effectivement le paramétrage SET FORMAT permet de prendre en compte des format exotique de conversion STRING DATE.

A +

**Waldar** · 30/01/2012, 12h38

Ou sinon, toujours écrire une date avec la fonction CONVERT, comme ça plus jamais de problème de localisation.

**olibara** · 30/01/2012, 14h09

Merci a tous !

**olibara** · 22/02/2012, 15h43

Bonjour

A la suite de ce Post J'avais cru comprendre que le format d'interpretation generique d'une date etait

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

yyyyMMdd

Je je decouvre que l'import export wizard n'aime pas du tout cela et qu'il exige

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

yyyy-MM-dd

En résumé la regle depends toujours du contexte !!

**Waldar** · 22/02/2012, 16h18

Je me permets de m'auto-citer :

Envoyé par Waldar

Ou sinon, toujours écrire une date avec la fonction CONVERT, comme ça plus jamais de problème de localisation.

**Serguei_TARASSOV** · 22/02/2012, 16h23

Envoyé par olibara

Bonjour
Je je decouvre que l'import export wizard n'aime pas du tout cela et qu'il exige

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

yyyy-MM-dd

En résumé la regle depends toujours du contexte !!

Par exemple,

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

convert(datetime, '2012-02-22 01:02:03', 121)

**olibara** · 22/02/2012, 16h43

Merci Seguei

Mais je cherche le format me permettant de faire un import a l'aide de l'import / export Wizard (j'ai +/- 400.000 rows a importer)

yyyy-MM-dd fonctionne pour les dates ayant une valeur
Par contre si je laisse le champ vide pour une date nulle j'ai une erreur de conversion

Je cherche encore

**Serguei_TARASSOV** · 22/02/2012, 16h52

Envoyé par olibara

Merci Seguei

Mais je cherche le format me permettant de faire un import a l'aide de l'import / export Wizard (j'ai +/- 400.000 rows a importer)

yyyy-MM-dd fonctionne pour les dates ayant une valeur
Par contre si je laisse le champ vide pour une date nulle j'ai une erreur de conversion

Je cherche encore

Es tu sûr que le pb est dans une valeur vide ? Ce code fonctionne correctement en 2008.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT
  convert(datetime, src_date, 121)
FROM
  (SELECT '2012-02-22 01:02:03' AS src_date
  UNION ALL 
  SELECT '' AS src_date
  UNION ALL 
  SELECT NULL AS src_date) t

Il est probable, que t'a des valeurs comme '9999-99-99' plutôt.

**olibara** · 22/02/2012, 17h21

Merci Serguei

Mais je parle maintenant d'un probleme de format de date non pas dans une commande SQL mais lors de l'usage de l'import / export Wizard

Une date ayant une valeur se presente dans une colonne tab délimitée

\t20120221\t

et nulle c'est

\t\t

Mais il semble que l'import export Wizard ne puisse pas traiter les valeurs vides !!

**Serguei_TARASSOV** · 23/02/2012, 10h48

Envoyé par olibara

Merci Serguei

Mais je parle maintenant d'un probleme de format de date non pas dans une commande SQL mais lors de l'usage de l'import / export Wizard

Une date ayant une valeur se presente dans une colonne tab délimitée

\t20120221\t

et nulle c'est

\t\t

Mais il semble que l'import export Wizard ne puisse pas traiter les valeurs vides !!

Pour moi "Import wizard" traite correctement les valeurs vides entre les tabulations.
Voir PJ : le fichier source et la capture d’écran.
Sinon essaie BCP.

**olibara** · 23/02/2012, 11h25

Merci Serguei

Tu utilise 2005 ou 2008 ?
J'ai lu qu'en 2008 le comportement avait changé

En tout cas j'ai contourné en mettant une valeur sentinelle et par apres je fais un update

**Serguei_TARASSOV** · 23/02/2012, 23h15

Envoyé par olibara

Merci Serguei

Tu utilise 2005 ou 2008 ?
J'ai lu qu'en 2008 le comportement avait changé

En tout cas j'ai contourné en mettant une valeur sentinelle et par apres je fais un update

Effectivement, il y a un problème en Import Wizard qui est contournée par une colonne dérivée en SSIS mais au niveau Wizard 2005 il n'y pas de solution à ma connaissance.

Alors, je te propose une méthode propre qui traite les valeurs vides comme "null" en 2005 et 2008.

Il te faut un fichier de format en plus (voir PJ)
Puis, on crée la table cible

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE dbo.import_test (
  int_value int NOT NULL,
  date_value datetime NULL,
  str_value nvarchar(255) NULL
)

Enfin, on fait l'import

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
BULK INSERT dbo.import_test
FROM 'P:\Temp\import_test.csv'
WITH
(
	FORMATFILE = 'C:\Temp\import_test.xml',
	ERRORFILE = 'C:\Temp\import_test.csv.err',
	BATCHSIZE = 1000000,
	CODEPAGE = 'ACP',
	MAXERRORS = 1,
	TABLOCK,
	FIRSTROW = 1
)

Espérons que cela aide

**olibara** · 24/02/2012, 17h39

Merci Serguei

Intéressant je n'avais jamais utilisé le Bulk Insert
C'est bon a connaitre

**tequillaman** · 30/05/2016, 11h44

Envoyé par SQLpro

Il ne s'agit pas d'une facétie, mais d'un comportement attendu et normatif.

Le format normatif de conversion de STRING en DATETIME dans SQL Server repose sur la norme SQL qui imposait le format 'AAAAMMJJ hh:mm:ss.nnn'
Ceci jusqu'à la norme SQL1999.

Quelque soit les règles du serveur, ce format passe outre le paramétrage de langue.

A partir de SQL Server 2008, qui a rajouté les type DATE, DATETIME2 et DATETIMEOFSSET, le format normatif ayant évolué, SQL Server prends désormais 'AAAA-MM-JJ ...' UNIQUEMENT pour les nouveaux types (afin de rester rétrocompatible).

Si vous aviez cherché un peu, vous seriez tombé sur l'article que nous avons écrit à ce sujet :
http://baptiste-wicht.developpez.com...-sql/datetime/

Effectivement le paramétrage SET FORMAT permet de prendre en compte des format exotique de conversion STRING DATE.

A +

Bonjour,
J'ai le même problème pour importer les dates de excel vers sql 2005. Le liens que tu met dans ta réponse ne fonctionne plus.