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.