Un format de Date compliqué ! DATETIME, TIMESTAMP, DATE ?

Version imprimable

22/11/2011, 15h34
Amarox

Un format de Date compliqué ! DATETIME, TIMESTAMP, DATE ?
Bonjour et merci d'avance pour votre aide,

Avant tout, Je précise que je simplifie le problème pour une meilleur compréhension :

Alors, je me trouve dans une impasse. J'ai une base de données SQL Server 2008 dans laquelle se trouve une table :
Code:

1 2 3 4 5 CREATE TABLE TableDate ( ID int NOT NULL, DATE1 date NULL, DATE2 date NULL, DATE3 date NULL)
Pour entrer des données dans cette table, j'ai comme point d'entré un fichier CSV que je ne peux pas toucher. A titre d'exemple je vous pose la première ligne de ce fichier :

Code:

123;01/09/11 00:00:00,000000000;15/09/11 00:00:00,000000000;20/09/11 00:00:00,000000000

Pour insérer les lignes de ce fichier dans ma table j'utilise le code suivant :
Code:

1 2 3 4 5 6 7 8 9 10 BULK INSERT TableDate FROM 'c:\Scripts SQL\export.csv' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, DATAFILETYPE = 'char' ) GO
Enfin l'erreur retournée :
Code:

1 2 Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (DATE1).
J'ai bien compris que le problème venait de la date. Le type date n'accepte pas la complexité de '15/09/11 00:00:00,000000000'. J'ai donc essayé les autres types :
- datetime : marche pas
- timestamp : marche pour DATE1 et le problème se décale à DATE2. Si je mets tout en timestamp, une erreur me dit que je n'ai droit qu'à un seul timestamp.

Je suis donc bloqué. Je pense que la solution serai de pouvoir, à la création de la base, préciser le format de la date qui va être passé lors de l'INSERT. un format de type jj/mm/aa hh:mm:ss,ms

Quelqu'un peut-il m'aider ?

Merci
22/11/2011, 17h12
Jinroh77

Dans mes souvenirs le BULK fonctionne également avec un fichier .fmt qui permet de définir le format d'entrée du fichier.. a voir de ce côté peut-être.
Sinon, SSIS c'est bien aussi pour faire de l'insert de masse.
22/11/2011, 17h25
zinzineti
Citation:
Envoyé par Amarox

Bonjour et merci d'avance pour votre aide,

Avant tout, Je précise que je simplifie le problème pour une meilleur compréhension :

Alors, je me trouve dans une impasse. J'ai une base de données SQL Server 2008 dans laquelle se trouve une table :

Code:

1 2 3 4 5 CREATE TABLE _TableDate ( ID int NOT NULL, DATE1 date NULL, DATE2 date NULL, DATE3 date NULL)

Code:

123;01/09/11 00:00:00,000000000;15/09/11 00:00:00,000000000;20/09/11 00:00:00,000000000

Pour insérer les lignes de ce fichier dans ma table j'utilise le code suivant :

Code:

1 2 3 4 5 6 7 8 9 10 BULK INSERT TableDate FROM 'c:\Scripts SQL\export.csv' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2, DATAFILETYPE = 'char' ) GO
D'abord tu as _TableDate comme table et tu fais une insertion dans TableDate

Mais le vrai problème c'est que tes colonnes sont de type DATE et veux insérer une valeur du genre 01/09/11 00:00:00,000000000
Lis ceci

au fait la virgule qu'il y a là représente quoi ? une date c'est au format AAAA-MM-JJ

Si tu es dans l'urgence alors créer une table temporaire en remplaçant les types DATE par varchar (50) et charge les données du fichier dans cette table temporaire et après tu te débrouille pour charger la table TableDate avec quelque du genre et des CAST, SUBSTRING , ...
Code:

1 2 INSERT INTO TableDate (ID,DATE1,DATE2,DATE3) SELECT ..... FROM TableTemporaire
22/11/2011, 18h20
Amarox

Merci,

Je dois finir ce point demain soir, ordre du chef ! Je suis chez moi la, j'ai bien lu vos posts, je reviens vers vous des demain.

Bonne soiré, merci encore
23/11/2011, 07h34
iberserk

Le

Code:

SET DATEFORMAT='ymd'

ne fonctionne pas avec BULKINSERT?

Jamais essayé...
23/11/2011, 11h14
Amarox

Citation:

Envoyé par zinzineti

D'abord tu as _TableDate comme table et tu fais une insertion dans TableDate

Merci j'ai corrigé,

Citation:

Mais le vrai problème c'est que tes colonnes sont de type DATE et veux insérer une valeur du genre 01/09/11 00:00:00,000000000
au fait la virgule qu'il y a là représente quoi ? une date c'est au format AAAA-MM-JJ

Le fichier CSV est un extract d'une base oracle. Je pense que les zéro après la virgule sont les millisecondes.

Sinon, je poursuit les tests sur fichier .fmt et SET DATEFORMAT='ymd'

merci à vous

Essayez :

Code:

1
2
3
4
5
6
CREATE TABLE tmp_TableDate (
	ID int NOT NULL,
	DATE1 NVARCHAR(60) NULL,
	DATE2 NVARCHAR(60) NULL,
	DATE3 NVARCHAR(60) NULL);
GO

Code:

1
2
3
4
5
6
7
8
9
10
BULK INSERT tmp_TableDate
FROM 'c:\Scripts SQL\export.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
FIRSTROW = 2,
DATAFILETYPE = 'char'
)
GO

Code:

1
2
3
4
5
6
SET DATEFORMAT DMY;
 
INSERT INTO TableDate (ID, DATE1, DATE2, DATE3)
SELECT ID, REPLACE(DATE1, ',', '.'), REPLACE(DATE2, ',', '.'), REPLACE(DATE3, ',', '.'), 
FROM    tmp_TableDate
GO

Code:

DROP TABLE tmp_TableDate

A +

28/11/2011, 14h54
Amarox
SQL Pro, merci pour ton aide, c'est vrai que le plus important était de modifier le champ date pour que SQL Server comprenne le format.

Je cherchais trop à résoudre le problème coté insertion alors que c'était plus simple de modifier le select qui créé le CSV. J'ai donc contacté la personne qui me fournissé le CSV en lui demandant d'appliquer pour chaque champ la logique suivante :
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT ID int NOT NULL, SUBSTR(DATE1,7,2)|| SUBSTR(DATE1,4,2)|| SUBSTR(DATE1,1,2) as DATE1, ... FROM TableDate
Merci à tous pour votre participation, je suis sur que les codes sql me serviront pour une autre fois.

Bonne semaine