SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 New instance refusing british date format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattt
Posting Yak Master

194 Posts

Posted - 04/11/2013 :  11:37:54  Show Profile  Reply with Quote
Hi,

On our usual production server, we have a read in task that uses the .net BulkCopyManager class to read delineated text files and push them into sql. It's always worked without a problem.

So I was pretty surprised when we built a new server and tried to run the same job against the same data to find that it choked on a particular file which had loaded fine before. The error was converting a string into a date column.

On investigation it turned out that the problem was that this particular file had its dates in British format (DD/MM/YYY) whereas the other files were in universal format (YYYY-MM-DD).

I am assuming that I've neglected to switch on some option or feature that allows SQL server to recognise the UK format as valid. But I can't find it. Can anyone point me in the right direction? Or, if I'm wrong, suggest what on earth else the problem is?

Cheers,
Matt

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 04/11/2013 :  12:35:22  Show Profile  Reply with Quote
I suspect it has to do with your language setting. Take a look at this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=182832

1. Find your current language: You can use "SELECT @@LANGUAGE".
2. Find the date format setting for it. Use "select * from sys.syslanguages where name = (select @@LANGUAGE)" You probably will see mdy.

If that is the case, language setting is the problem.

Now, how you would solve that

- if you can change the language setting on your server (it is in the advanced tab of the server properties dialog) that would fix the problem. But that may not be something that you want to do. Even if you do that, users already created may retaint their default language. In any case be very careful if you choose to go that route.

- you could change your processes to import the data a string to a staging table and then use a query where specify dmy format explicitly to move it to your destination table.

- you may be able to use a format file for the bulk copy; i don't know enough details to say anything useful.

Go to Top of Page

mattt
Posting Yak Master

194 Posts

Posted - 04/12/2013 :  04:02:13  Show Profile  Reply with Quote
Thanks. I had a good old mess about with the languages on SQL Server to no avail.

However it looks as though it could be a .net language error. If you run BULK INSERT against the server directly, it reads date formats as you'd expect dependent on the language settings. I had assumed that since the error was being thrown on bulk insert that it was a sql problem but, of course, .net has it's own BulkCopy class which will run on its own thread with its own language settings.

Going to play with that now, and see if it fixes - will report here if it does.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1671 Posts

Posted - 05/10/2013 :  18:59:32  Show Profile  Reply with Quote
You can SET DATEFORMAT at the session level if that would help. Per BOL, "SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE."

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Edited by - Bustaz Kool on 05/10/2013 19:00:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000