Using bcp Utility in SQL

devrishi · November 12th, 2003, 03:59 AM

hi,

When we use bcp over SQL Data to transfer data to text file, it is giving me Date in Reverse order (Year-Month-Date). Visual Foxpro finds it difficult to convert it into date field.

Is there any way out to convert the date or any other way I can use 'bcp utility' so that it gives me Dates in dd/mm/yyyy format.

Please help. Its urgent.

Regards

Devrishi

simtap · November 13th, 2003, 03:01 AM

How about converting the date field within the query? Something like this should work:

Code:

SELECT *, CONVERT(CHAR(10), DateField, 103) FROM tblTable

Cheers,
Brendan.

devrishi · November 13th, 2003, 05:07 AM

hi Brendan,

thanks for ur help. It works. but I'm using bcp (bulk copy) utility. Is there any way in which I can convert datetime field in bcp?

Regards

Devrishi

Gary · November 24th, 2004, 03:43 AM

Hi Devrishi

What Brendan means (I believe) is to use the queryout flag in BCP.

Ie. BCP "SELECT TOP 10 * FROM Pubs..tblTable" queryout -c -E -Usa -Ppassword -SServername

Substitute the SQL for whatever you want (function or not) to get the format you desire.

Hope this helps.

Gary

Gary · November 24th, 2004, 03:47 AM

Oops.

Missed out the target text File.

How about this instead.

Ie. BCP "SELECT *, CONVERT(CHAR(10), DateField, 103) FROM tblTable" queryout Text.txt -c -E -Usa -Ppassword -SServername

Gary