Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > SQL Server 2008
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2008 General discussion of SQL Server *2008* version only - not related to a specific book.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2008 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 29th, 2013, 08:54 AM
Authorized User
Points: 84, Level: 1
Points: 84, Level: 1 Points: 84, Level: 1 Points: 84, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2004
Location: London, , United Kingdom.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default UK Dates in Tab delimited file not importing correctly.

I've posted this on SSC as well but so far not had a suitable response. Hoping that I get an answer here.

I have a range of some 40 tab delimited txt files to load into a SQL Server 2008R2 database. These range in size from 2k to 14GB. Dates in these files are in the DDMMYY, DDMMYYYY, DD/MM/YY, DD/MM/YYYY (or YYYYMMDD being imported as varchar) format. Some columns can have a mixture of 2 and 4 digit years.

The box on which SQL Server has been installed was for some unknown reason set to US (I'm in the UK, the box is in the UK, the data is all UK data and we are a UK organisation). Apparently this is now UK localisation. I cannot change this on this pre production environment anyway. I believe that the live environment is correct. SQL Server, all of the users etc, I have changed to the correct UK settings. Originally when they set the instance up they defaulted it all to US and I had to change it to British English

All the receiving tables have been created with the correct data types set. Dates have been set to datetime.

When using SSIS to import the data (I can only use SSIS for operational reasons), the dates are incorrectly imported. Originally I thought converting to a US date format but further investigation shows many inconsistencies.

If I bring the date data in as say varchar and then validate using the isdate function SQL Server returns 1 as a valid UK date. If I alter the table and add a date (not datetime) column and update the new column the dates are converted correctly. If it's a datetime column the data is incorrect although not as inconsistent as via SSIS.

I have also tested this process on two other environments (both of which I configured personally from scratch some time ago) and I can not replicate this issue with the test data I have.

Interestingly, when using the SSIS wizard on my machines the Locale defaults to English (United Kingdom). On the their machine it defualts to English (United States) and I have to change the locale manually to be UK

As an adendum to this, We have also checked the default user in the registry and this is set correctly too.

So (finally) my questions are as follows:-

Why can't SSIS correctly convert the dates to the UK date format? Why does it think it needs to convert it to US or derrivative thereof?

How can I get SSIS to import the data correctly or am I going to have to use the subsequent update method described above.

I can provide a sample data file, sample table structure and screen prints if requested.

Many thanks in advance.

John
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying data from excel to txt file with tab delimited Deepikavispute BOOK: Excel 2003 VBA Programmer's Reference 1 December 10th, 2012 02:40 AM
Write Tab-delimited text file VikramMullick Pro VB.NET 2002/2003 1 May 5th, 2006 11:18 AM
Tab delimited Split ajindal General .NET 1 April 25th, 2006 08:20 AM
Importing Delimited Text sdilucca Access 1 February 23rd, 2006 01:20 PM
Upload tab delimited into Access? mariakovacs Classic ASP Databases 0 October 10th, 2003 10:54 AM



All times are GMT -4. The time now is 10:12 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.