BCP/Bulk Insert Datetime Problem,

Hi,

I am having problems getting BCP/Bulk Insert to insert dates properly.

The dates are being presented to us as,

dd/MM/yyyy hh:mm:ss

however, when importing into SQL Server as a datetime field via bcp/bulk insert, they are being inserted as

MM/dd/yyyy hh:mm:ss

In Australia, Windows region date setting set correctly and using Latin1_General_CI_AS collation.

Using the following format file (just the relevant sample from the file),

8.0
9
1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS

I tried using

1 SQLDATETIME 0 17 "," 1 startDateTime ""

however this did not work.

Please let me know if any additional info needed.

Thanks!Dates are stored internally as numbers...the rest is just a presentation issue...look up convert in BOLsql

BCP/Bulk Insert and Incoming Dates

Ladies and Gentlemen
Is their a way to force BCP to accept a Date Value of
'000000'
instead of giving an error?
I am using BULK INSERT with a FORMAT FILE.
Mark MossHi Mark
The easiest way would be to use a staging table and then you can cater for
this when you update the main table. Another alternative would be to use DTS
an AnctiveX transform or possibly a lookup.
John
"Mark Moss" wrote:

> Ladies and Gentlemen
>
> Is their a way to force BCP to accept a Date Value of
> '000000'
> instead of giving an error?
>
> I am using BULK INSERT with a FORMAT FILE.
>
> Mark Moss
>
>

bcp export stored procedure with a date in the statement

Hi

Please can someone help me with the statement below. I am trying to export, via bcp a stored procedure which requires two dates and cannot seem to work out the correct way of typing it into the statement. I know that the dates are meant to have an ' around them but cant work out how to get this concatenated correctly.

Any help would be appreciated.

Paul

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP 05/01/07, 01/01/07" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'
exec master..xp_cmdshell @.sq

use the following query...

Code Snippet

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

|||Thanks very much for your help

bcp and invalid character value set

Hello
I've read that there is a bug in sql server 2000 when doing a bcp command
with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
about this hotfix and from where we can get it
thx
Hi
Can you point to where there is a reference to this "bug"?
The -R flag will use the regional setting of the client machine and there
was a fix in SP1 where this was being ignored.
You will need to make sure that you also have the correct version of the
client tools on the machine that you are using.
John
"Pascal Haddad" <ph@.sabaipc.com> wrote in message
news:u8LAsf6qFHA.3096@.TK2MSFTNGP15.phx.gbl...
> Hello
> I've read that there is a bug in sql server 2000 when doing a bcp command
> with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
> in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
> about this hotfix and from where we can get it
> thx
>
>

bcp and invalid character value set

Hello
I've read that there is a bug in sql server 2000 when doing a bcp command
with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
about this hotfix and from where we can get it
thxHi
Can you point to where there is a reference to this "bug"?
The -R flag will use the regional setting of the client machine and there
was a fix in SP1 where this was being ignored.
You will need to make sure that you also have the correct version of the
client tools on the machine that you are using.
John
"Pascal Haddad" <ph@.sabaipc.com> wrote in message
news:u8LAsf6qFHA.3096@.TK2MSFTNGP15.phx.gbl...
> Hello
> I've read that there is a bug in sql server 2000 when doing a bcp command
> with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
> in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
> about this hotfix and from where we can get it
> thx
>
>

bcp and invalid character value set

Hello
I've read that there is a bug in sql server 2000 when doing a bcp command
with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
about this hotfix and from where we can get it
thx
Do you have anything more specific on this? What is the behavior that is
failing?
| From: "Pascal Haddad" <ph@.sabaipc.com>
| Subject: bcp and invalid character value set
| Date: Sat, 27 Aug 2005 21:33:25 +0200
| Lines: 9
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#GfznXzqFHA.3352@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 213.175.179.18
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2623
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello
|
| I've read that there is a bug in sql server 2000 when doing a bcp command
| with dates in dd-mm-yyyy format. and to fix it there is a hotfix
contained
| in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
| about this hotfix and from where we can get it
| thx
|
|
|

bcp and invalid character value set

Hello
I've read that there is a bug in sql server 2000 when doing a bcp command
with dates in dd-mm-yyyy format. and to fix it there is a hotfix contained
in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
about this hotfix and from where we can get it
thxDo you have anything more specific on this? What is the behavior that is
failing?
| From: "Pascal Haddad" <ph@.sabaipc.com>
| Subject: bcp and invalid character value set
| Date: Sat, 27 Aug 2005 21:33:25 +0200
| Lines: 9
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#GfznXzqFHA.3352@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 213.175.179.18
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2623
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Hello
|
| I've read that there is a bug in sql server 2000 when doing a bcp command
| with dates in dd-mm-yyyy format. and to fix it there is a hotfix
contained
| in sp4 . I ve installed the sp4 but the bug remains anybody has an idea
| about this hotfix and from where we can get it
| thx
|
|
|

BCP / Bulk Insert Date Problems,

Hi,
I am having problems getting BCP/Bulk Insert to insert
dates properly.
The dates are being presented to us as,
dd/MM/yyyy hh:mm:ss
however, when importing into SQL Server as a datetime
field via bcp/bulk insert, they are being inserted as
MM/dd/yyyy hh:mm:ss
In Australia, Windows region date setting set correctly
and using Latin1_General_CI_AS collation.
Using the following format file (just the relevant sample
from the file),
8.0
9
1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
I tried using
1 SQLDATETIME 0 17 "," 1 startDateTime ""
however this did not work.
Please let me know if any additional info needed.To circumvent the problem load all data into a Holding Table, load the date
data into a varchar column. Then INSERT the data into it's destination with
a select statement that CONVERTs the varchar dates into datetime. Specify
the additional STYLE parameter in your CONVERT to ensure you capture the
dates correctly, i beleive dd/mm/yyyy is STYLE 103.
HTH
Ryan Waight, MCDBA, MCSE
"Mallen" <mallen@.nospam.bigpond.net.au> wrote in message
news:04ef01c3a981$07cd3470$a501280a@.phx.gbl...
> Hi,
> I am having problems getting BCP/Bulk Insert to insert
> dates properly.
> The dates are being presented to us as,
> dd/MM/yyyy hh:mm:ss
> however, when importing into SQL Server as a datetime
> field via bcp/bulk insert, they are being inserted as
> MM/dd/yyyy hh:mm:ss
> In Australia, Windows region date setting set correctly
> and using Latin1_General_CI_AS collation.
> Using the following format file (just the relevant sample
> from the file),
> 8.0
> 9
> 1 SQLCHAR 0 17 "," 1 startDateTime Latin1_General_CI_AS
> I tried using
> 1 SQLDATETIME 0 17 "," 1 startDateTime ""
> however this did not work.
> Please let me know if any additional info needed.