bcp og Datoer

Er det et problem i den anden ende? Det format, den giver dig, er ISO-format og skulle ifølge dokumentationen altid kunne genkendes af en SQL Server

Du skal bruge Queryout out:

Bcp "SELECT CONVERT(varchar,DATOFIELD,DATEFORMAT)...." queryout -q ....

Det var forresten et svar...

In the database the data is stored as some large number. How you see the date is controlled by the regional settings (amongs other things)
BCP obvioulsy has too put it in a format which is understood by any PC noo matter what that regional setting is set to. Try importing it again and it SHOULD end up OK!

Så er mit også et svar

AS Benny states this is ISO format. I would suggest you keep it a sthat in the file and take it into concideration IF you get problems BCP'ing it in to another dB again.

Hvad skal du bruge det til?

which comment/answer are you refering to ken?

Jeg vil vide hvad resultatet fra bcp'en skal bruges til. Hvis det er til import i et regneark er det jo meget relevant hvilket format datoen er i!

Det skal bruges til import til de selv samme tabeller som de er eksporteret fra!!

Excel also uses regional settings!

If you have a file with date in ISO format then it SHOULD be possible to import it into Access/Excel etc. on PC's where the regional settings are different. In doing so the date SHOULD be displayed in the correct format. If it were in US format then it MAY not be dipslaye correctly in DK/GB

nolle_k>>benny.tordrup burde have ret.

terry>> It might be another spreadsheet. How aboyt symphony 2.2

symphony, isnt that Lotus? Can you use dates in that? :o)

No, I have no idea, but does that mean it has to be in US format in symphony or what?

Problemet er ikke at formatet bliver forkert når jeg importerer men at BCP fejler med besked om Invalid Date format!!

Jeg har prøvet at fjerne -R der burde gør sådan så bcp'en benytter regional settings men det gør den åbenbart kun når den importerer og ikke når den eksporterer....
Det er sgu lidt belastende!

>>terry Symphony 2.2 is an old DOS Spreadshett. My point is, we did'nt now what the outpu should be used for.

ken>I also think benny's answer is correct, but I cant understand you saying that when you afterward mention symphony 2.2, whet does that have to do with SQL Server?

nolle_k>in you rquestion you didnt mention an ERROR, you also said that the format was yyyy-mm-dd!

ken> No we dont, and now we dont even know what nolle_k's problem is!
But I STILL aggee with benny,the date should be in ISO format so that MOST PC's should understand it, even in DK!

Proplemet er selvfølgelig til at starte med at dato formatet i den fil bcp'en laver er forkert. Dette resulterer så i, når jeg prøver at importerer selv samme fil på selv samme maskine 10 minutter senere, i en fejl der siger Invalid Date Format!!

>>terry I only thought that your answers where correct if you had to import the bcp output to SQL Server. But the question was about how to get the bcp date output in the format "MM/dd/yyyy". And the answer is, that you have explicit convert the date field to a varchar in the desired format. This requires the paramters -q (to accept quoted identifiers), queryout (to accept a query) and the sql statement. Have I done something to piss you off?

Så er det muligvis i den bcp import det går galt. Med mindre du bruger andet end bcp til at importere?

Fra Books Online (SQL Server 2000):

The bcp utility (which uses ODBC) can import datetime and smalldatetime values in character-mode data files using:
The default format used by DB-Library.


The format used by ODBC (yyyy-mm-dd hh:mm:ss[.f...]).
However, bcp does not use other formats supported by dbconvert.

bcp exports datetime and smalldatetime values using the ODBC default format.

Expect different results as compared to earlier versions of SQL Server. To bulk copy data in character mode between SQL Server 6.x and SQL Server 2000 servers, use the same bcp version (either SQL Server 6.x or SQL Server 2000) for both importing and exporting data.

To export data from a SQL Server 2000 server into a character-mode data file and later import that data using a DB-Library bulk copy application, use the SQL Server 6.x version of bcp.

For existing datetime or smalldatetime values in a character-mode data file in a format other than the DB-Library default:

Change the values to the DB-Library default format for continued use with SQL Server 6.x and SQL Server 2000 bcp.


Change the values to the ODBC format for use with SQL Server 2000 bcp.

Hvilken version bruger du af SQL Server

SQLServer 2000

Så fandt jeg den irriterende fejl!

Fejlen ligger i at i de data, der eksporteres og så importeres igen indeholde den karakter, som jeg bruger som felt seperator i min fil!! Damn!!!!!

Hvordan løser jeg så dette problem??? Jeg kunne selvfølgelig før jeg eksporterer søge efter denne karakter og så erstatte denne karakter med en prædefineret karakter sekvens jeg så erstatter igen når der importeres!!

//Nolle_K

Du kan benytte dig af -t "<NEW FIELD>" eller andet du ved ikke findes i databasen.

Hvad nu hvis brugeren har benyttet ALLE tænkelige karakterer???

Hvis du bruger -t "<@@NEWFIELD@@>" er jeg rimelig sikker på at denne kombination ikke findes i db'en. Du skal huske at bruge -q også. Den tager altså <@@NEWFIELD@@> og sætter ind som field delimiter.

Ellers kan du jo bruge:
-t "<@@Jeg er temmelig sikke på at dette ikke indgår nogle steder i databasen...#¤$@@##££@@>"

;-)

Lukker!