Problem with mm/dd/yyyy using import-export

Question

Problem with mm/dd/yyyy using import-export

Teresita Castro

SSCommitted

Points: 1568
More actions
September 3, 2007 at 5:19 pm

#174495

I have a text file that contain a fiel with mm/dd/yyyy, this text is exporting to a table in SQL Server 2000 using a package.
The problem is that if the user change the windows configuration ( we are in Mexico) and they put dd/mm/yyyy the information in my sql table is load wrong.

What Can I do to solve this sistuation?
Thanks ( I hope you undestand my English

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

What's the source of information for the text file? A GUI??? or ??? In other words, what puts the data into the text file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vladan SSC-Insane Points: 22072 More actions · Answer 2

The source file (text file you want to import) must be correct. Try working on this angle - make sure that it will not be entered incorrectly. If possible, make separate entries for month and day (e.g., a listbox with month names + listbox with day numbers).

Quite often a date written as mm/dd/yyyy has a valid interpretation in dd/mm/yyyy format, too - but it is an entirely different date. Therefore, a simple validity check is not enough. 01/05/2007 would pass the check, but is it 1st May or 5th January?

Problem is not on the side of import to SQL Server, it simply has to be solved in the software that creates the text files.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

What I was getting at is if the source is from a GUI, you must have 3 fields... 1 each for Month, Day, and Year and each field must have it's own validation. Then, when you save the date to the text file, it should be saved using the ISO format of YYYYMMDD... makes everybody happy...

If the file is being created by individual batch processes, the file format, as Vladan suggests, must be absolutely correct and identical in all cases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Teresita Castro SSCommitted Points: 1568 More actions · Answer 4

The text file is create by another aplication and every day the package delete the sql table and fill it with the information in that text file.

Thanks for your help if I change the format on the text file to YYYYMMDD it works just fine

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

You bet... thanks for the feedback...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)