Date Format in BCP bulk export
-
Hello everyone, I'm using bcp.exe to export some data from an SQL Server 2005 database to a txt file that can be used in some other old unix system. Thing is, once the export is done, exported rows look like this:
0730280001868 38 20040005232004-03-24 00:00:00.0002004-04-19 00:00:00.000
Dates are in a yyyy-MM-dd hh:mm:ss.sss format. However, I need datetime values to get exported in a dd/MM/year format. According to documentation in MSDN, format in bcp import/export operations is done through a format file (an xml file in this case), so any change in the format should be done by modifying this file. Currently, my format file looks like this:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="POL_NumPoliza" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="CIA_Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="SIN_Alias" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SIN_FechaSiniestro" xsi:type="SQLVARYCHAR" LENGTH="8"/>
<COLUMN SOURCE="5" NAME="SIN_FechaParte" xsi:type="SQLVARYCHAR" LENGTH="8"/>
</ROW>
</BCPFORMAT>Considering I've changed almost everything that can be changed in that file [datatypes, collations, lengths etc], my question is, does anyone have an idea on how to force bcp to write datetime values in a particular format (just like using
FORMAT!) when writing the text file? Thanks in advance !Kazz
"Users are there to click on things, not think. Let the archs do the damn thinking."
-
Hello everyone, I'm using bcp.exe to export some data from an SQL Server 2005 database to a txt file that can be used in some other old unix system. Thing is, once the export is done, exported rows look like this:
0730280001868 38 20040005232004-03-24 00:00:00.0002004-04-19 00:00:00.000
Dates are in a yyyy-MM-dd hh:mm:ss.sss format. However, I need datetime values to get exported in a dd/MM/year format. According to documentation in MSDN, format in bcp import/export operations is done through a format file (an xml file in this case), so any change in the format should be done by modifying this file. Currently, my format file looks like this:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="POL_NumPoliza" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="CIA_Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="SIN_Alias" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SIN_FechaSiniestro" xsi:type="SQLVARYCHAR" LENGTH="8"/>
<COLUMN SOURCE="5" NAME="SIN_FechaParte" xsi:type="SQLVARYCHAR" LENGTH="8"/>
</ROW>
</BCPFORMAT>Considering I've changed almost everything that can be changed in that file [datatypes, collations, lengths etc], my question is, does anyone have an idea on how to force bcp to write datetime values in a particular format (just like using
FORMAT!) when writing the text file? Thanks in advance !Kazz
"Users are there to click on things, not think. Let the archs do the damn thinking."
Hi, you didn't include the bcp command to the post, but I believe that you are exporting a table directly (like:
bcp tablename...). Instead of that, try exporting based on a query where you convert the datetime colun to varchar in desired format and use that query in bcp. For example:bcp "SELECT CONVERT(varchar(50), DatetimeColumnName, 103), ..." queryout, ...
Mika