BCP date format issue while exporting data from SQL server to csv file

Question

BCP date format issue while exporting data from SQL server to csv file

Alexander S 1

I'm using BCP command in SQL server to generate data's in csv file.
issue: SQL output value is 25/07/2021 but in csv same column value showing as 25-07-2021. Column data type is already defined as Nvarchar(30) in table.

I want to show same sql output format in csv. kindly help on this.

Yitzhak Khabinsky 26,586 Reputation points

2021-07-25T17:22:01.633+00:00

@Alexander S ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Viorel 122.6K Reputation points

2021-07-25T18:11:29.25+00:00

It looks strange that a nvarchar column is distorted. What command parameters are you using and why the dates are stored as text?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-25T20:24:02.01+00:00

We definitely need to see the command you are using as well as the table definition and some sample data that exhibits the issue.
Anonymous

2021-07-26T02:13:31.633+00:00

Hi,
The "windows-server-powershell" tag focuses on technical questions about explanation and correction of commands, advice for PowerShell scripts, and troubleshooting PowerShell-related issues. I removed it as this issue is more related to SQL. Thanks for your understanding.
Alexander S 1 Reputation point

2021-07-26T02:47:38.187+00:00

Hi Guys,

Thanks for your replies. Only issue is Doc_date format.

Find below details

SQL Query:
Declare @filename Nvarchar(100)
Declare @commandcommand varchar(8000)
select @filename = 'GL_Report.csv'

SELECT @commandcommand ='bcp "select COMP_CODE,convert(nvarchar(50),DR_CR_CODE),CURRENCY,COST_CENTER,COST_DESC,convert(nvarchar(50),Doc_Date),PAY_PERIOD,SAP_ACC_CODE,GL_ACC_NAME,convert(nvarchar(50),DR_CR_AMOUNT),extra_1,extra_2 from GL_REPORT_TAB_DCUBE_auto(Nolock) " queryout E:\GL_Report\GL_File\Processed\'+@filename+' -c -t, -T -S '+@@servername

exec master..xp_cmdshell @commandcommand

SQL Output:

117750-sql-output.png

CSV file output:

![117708-csv-output.png][2]
Alexander S 1 Reputation point

2021-07-26T02:48:59.15+00:00

Hi Guys,

Thanks for your replies. Only issue is Doc_date format.

Find below details

SQL Query:
Declare @filename Nvarchar(100)
Declare @commandcommand varchar(8000)
select @filename = 'GL_Report.csv'

SELECT @commandcommand ='bcp "select COMP_CODE,convert(nvarchar(50),DR_CR_CODE),CURRENCY,COST_CENTER,COST_DESC,convert(nvarchar(50),Doc_Date),PAY_PERIOD,SAP_ACC_CODE,GL_ACC_NAME,convert(nvarchar(50),DR_CR_AMOUNT),extra_1,extra_2 from GL_REPORT_TAB_DCUBE_auto(Nolock) " queryout E:\GL_Report\GL_File\Processed\'+@filename+' -c -t, -T -S '+@@servername

exec master..xp_cmdshell @commandcommand

SQL Output:

CSV file output:

!
Seeya Xi-MSFT 16,586 Reputation points

2021-08-04T01:30:03.737+00:00

Hi @Alexander S ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

4 answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-07-25T17:22:01.633+00:00

@Alexander S ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Viorel 122.6K Reputation points

2021-07-25T18:11:29.25+00:00

It looks strange that a nvarchar column is distorted. What command parameters are you using and why the dates are stored as text?
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2021-07-25T20:24:02.01+00:00

We definitely need to see the command you are using as well as the table definition and some sample data that exhibits the issue.
Anonymous

2021-07-26T02:13:31.633+00:00

Hi,
The "windows-server-powershell" tag focuses on technical questions about explanation and correction of commands, advice for PowerShell scripts, and troubleshooting PowerShell-related issues. I removed it as this issue is more related to SQL. Thanks for your understanding.
Alexander S 1 Reputation point

2021-07-26T02:47:38.187+00:00

Hi Guys,

Thanks for your replies. Only issue is Doc_date format.

Find below details

SQL Query:
Declare @filename Nvarchar(100)
Declare @commandcommand varchar(8000)
select @filename = 'GL_Report.csv'

SELECT @commandcommand ='bcp "select COMP_CODE,convert(nvarchar(50),DR_CR_CODE),CURRENCY,COST_CENTER,COST_DESC,convert(nvarchar(50),Doc_Date),PAY_PERIOD,SAP_ACC_CODE,GL_ACC_NAME,convert(nvarchar(50),DR_CR_AMOUNT),extra_1,extra_2 from GL_REPORT_TAB_DCUBE_auto(Nolock) " queryout E:\GL_Report\GL_File\Processed\'+@filename+' -c -t, -T -S '+@@servername

exec master..xp_cmdshell @commandcommand

SQL Output:

117750-sql-output.png

CSV file output:

![117708-csv-output.png][2]
Alexander S 1 Reputation point

2021-07-26T02:48:59.15+00:00

Hi Guys,

Thanks for your replies. Only issue is Doc_date format.

Find below details

SQL Query:
Declare @filename Nvarchar(100)
Declare @commandcommand varchar(8000)
select @filename = 'GL_Report.csv'

SELECT @commandcommand ='bcp "select COMP_CODE,convert(nvarchar(50),DR_CR_CODE),CURRENCY,COST_CENTER,COST_DESC,convert(nvarchar(50),Doc_Date),PAY_PERIOD,SAP_ACC_CODE,GL_ACC_NAME,convert(nvarchar(50),DR_CR_AMOUNT),extra_1,extra_2 from GL_REPORT_TAB_DCUBE_auto(Nolock) " queryout E:\GL_Report\GL_File\Processed\'+@filename+' -c -t, -T -S '+@@servername

exec master..xp_cmdshell @commandcommand

SQL Output:

CSV file output:

!
Seeya Xi-MSFT 16,586 Reputation points

2021-08-04T01:30:03.737+00:00

Hi @Alexander S ,

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Hi Guys,

Thanks for your replies. Only issue is Doc_date format.

Find below details

SQL Query:
Declare @filename Nvarchar(100)
Declare @commandcommand varchar(8000)
select @filename = 'GL_Report.csv'

SELECT @commandcommand ='bcp "select COMP_CODE,convert(nvarchar(50),DR_CR_CODE),CURRENCY,COST_CENTER,COST_DESC,convert(nvarchar(50),Doc_Date),PAY_PERIOD,SAP_ACC_CODE,GL_ACC_NAME,convert(nvarchar(50),DR_CR_AMOUNT),extra_1,extra_2 from GL_REPORT_TAB_DCUBE_auto(Nolock) " queryout E:\GL_Report\GL_File\Processed\'+@filename+' -c -t, -T -S '+@@servername

exec master..xp_cmdshell @commandcommand

SQL Output:

CSV file output:

![117708-csv-output.png ]2

Answer 2

Hi @Alexander S ,

CSV takes the time format of windows by default. Modify the date and time format in the regional settings in the control panel, make sure the format is yyyy-mm-dd.
If the above method does not work, you can export the time format to text format with adding Double quotes.

Best regards,
Seeya

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Viorel 122.6K

Try this function:

convert(nvarchar(50), Doc_Date, 103)

The format function can be used too.

Check the CSV file using Notepad. If you are viewing it using other programs such as Excel, then you will probably have to adjust the cell formats in Excel. It is not a BCP problem.

Answer 4

Erland Sommarskog 121.4K MVP Volunteer Moderator

So where are you looking at the CSV file? The image you post seems to come from Excel. Look at the CSV file from Notepad instead. Excel has its own ideas and is often too smart for its own good.

Share via

BCP date format issue while exporting data from SQL server to csv file

4 answers

Your answer