bcp and dmy date formats
Ian Hingley
I need to bcp some data from a text file into an existing table. SQL
server is set to British English and displays dates as dd/mm/yyyy. It
stores them as mm/dd/yyyy.
My text file contains dd/mm/yyyy date data but my bcp import fails on
invalid date format. If I edit an example text file to get the data
to be mm/dd/yyyy it works OK.
Anyone got experience of getting bcp to work with dd/mm/yyyy date
data?
Thanks
Ian Hingley
Simon Hayes
couple of ideas:
1. Use the -R switch to BCP to use the client's regional settings for date -
this only applies if the client has the correct settings, of course, and
would also affect currency data, if you have any in your input file.
2. Probably a better option is to use DTS - the Transform Data task has a
convenient date format option which allows you to handle almost any date
format transformation you might need.
3. If neither of the previous options are suitable in your environment, you
can look at BCP into a staging table, then converting the data before
inserting into the destination table.
By the way, SQL Server is not storing the dates as mm/dd/yyyy - dates are
stored internally as an offset from a base date. The format you see when you
retrieve datetime values is determined by whatever client settings you're
using (which may be the default server settings). The BOL topic "Writing
International Transact-SQL Statements" has information about using a date
format that is always interpreted correctly regardless of the server or
client settings. But if you have no control over the format of the source
file, then you'll have to handle it as it is now.
Simon
"Ian Hingley" <ian.h...@swiftlg.com> wrote in message
news:ffee2ef.03061...@posting.google.com...
Erland Sommarskog
> I need to bcp some data from a text file into an existing table. SQL
> server is set to British English and displays dates as dd/mm/yyyy. It
> stores them as mm/dd/yyyy.
> My text file contains dd/mm/yyyy date data but my bcp import fails on
> invalid date format. If I edit an example text file to get the data
> to be mm/dd/yyyy it works OK.
To add to Simon's response. SQL Server does not display dates in any
format. If you say "SELECT datecol", SQL Server passes the date column
in binary format to the client, and the client presents the data. Hopefully
by using the regional settings in Windows.
An alternative to the suggestions offered by Simon is to set the
default language (sp_defaultlanguage) for the SQL Server login you use
for the BCP operation to British.
--
Erland Sommarskog, SQL Server MVP, som...@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
ian hingley
button! What I meant to imply was that if I use SQL to select a
datetime field it is like this: 2001-12-23 17:16:29.000 If I use Open
Table - Return all Rows it shows: 23/12/2001.
Anyway, back to the issue. I would be happy using DTS as it just
'handles' dates OK. My colleague however has over 1000 tables and would
far rather use a bcp batch file than construct a massive DTS 'diagram'.
These are a bit of a pain I agree.
My test bcp line is as follows:
bcp agtest..customer in "c:\temp\customer.txt" -c -t"|" -T -e
"c:\temp\customer_bcperr.txt"
It falls over with:
#@ Row 1, Column 70: Invalid date format @#
Column 70 contains the data 27/01/1998
Using -R had no effect, and my language is already British.
Any more clues would be appreciated.
Thanks again
Ian Hingley
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Erland Sommarskog
> bcp agtest..customer in "c:\temp\customer.txt" -c -t"|" -T -e
> "c:\temp\customer_bcperr.txt"
>
> It falls over with:
>
> #@ Row 1, Column 70: Invalid date format @#
>
> Column 70 contains the data 27/01/1998
>
> Using -R had no effect, and my language is already British.
I tried to change my regional settings and use -R, and indeed it
did not work out. Neither did it work to run the BCP with an SQL
user that had British as his language.
It could be that -R looks at the system locale. That is, there can be
two locales on a machine. One which you as a current user have, and
one which is the default for new users. The system locale on your
machine could be US English. This may seem like a silly thing to do,
but I have run into programs that does this.
ian hingley
Control Panel. I am logged to the machine as Administrator and using -T
in the bcp command, so passing these trusted credentials to SQL Server.
Anything else I can try?
Regards
Erland Sommarskog
> Thanks Erland. My system locale is set to English(United Kingdom) in
> Control Panel. I am logged to the machine as Administrator and using -T
> in the bcp command, so passing these trusted credentials to SQL Server.
Sorry for coming late with the followup, but I had to do some research.
And it turns out that my suspicion is correct. BCP -R does indeed look
at the system locale. I believed that I had Swedish as my system locale,
but I tried with a program that I know looks at the system locale, and
this program failed to work, because it got a US-formatted date.
You say your system locale is set to English (UK) in the Control Panel.
However, I don't think there is a direct way to see which system locale you
have. One place to look at is in the registry
HKEY_USERS\.DEFAULT\Control Panel\Internationals\sShortDate. On my
XP Partition this setting was M/d/yyyy. (Strangely, all other settings on
this key appeared to fit be for Swedish.)
To correct this I had to go the third tab (Advanced) in the Regional
Settings applet. At the bottom of this tab, there is a "Default user account
settings". Only checking that and press Apply did not help. I also had
to change the upper frame "Language for non-Unicode programs" which I
had left at English (US). I changed this to Swedish, checked "Default
user account settings" and rebooted. After this I could bulk load the
date 19/06/2003 when I used -R with bcp. Intreresting enough, with
Swedish settings, although the Swedish date format is 2003-06-19.
You never said which operating system you are using. The dialog in
for Regional settings in Win2003 appears to very similar to the one
in WinXP. On the other hand in Win2000 it is different. I know there is
a Set Default button.