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 utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

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.