bcp select where date between problem

Question

question

Emile asked Dec 23, '11

bcp select where date between problem

The following works: bcp tkcsdb.DBO.VP_TOTALS out c:\tkcsdb.dat -n -Usa -Pabc123de and returns ~750k records. I'd like to limit the results by adding a date check against a field: SELECT * FROM DBO.VP_TOTALS WHERE ADJUSTEDAPPLYDATE between '12/16/2011' and '12/31/2011'; which returns ~1200 records when fed to DTS. I've tried dozens of variations using bcp and gotten only mixed error codes as a result. This is on Windows Server 2003 with MS SQL Server 80.

sql-server-2008 bcp date test

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-12-24T13:54:22Z

silent_worker answered Dec 24, '11

bcp "select * from dbname..cm_date_lkp where businessdate between '2009-09-01' and '2010-10-01' " queryout C:\result1.csv -S servername -T -c

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

silent_worker commented · Dec 24, 2011 at 01:55 PM

bcp "select * from dbname..cm_date_lkp wher e businessdate between '2009-09-01' and '2010-10-01' " queryout C:\result1.csv -Sservername -T -c

0 ·

Usman Butt commented · Dec 25, 2011 at 10:12 PM

As @silent_worker has shown in the example, you need to use "queryout" option of the bcp to do that. You can see good examples in BOL e.g. http://msdn.microsoft.com/en-us/library/ms162802.aspx

0 ·

Answer 2 · 2011-12-27T10:49:09Z

Emile answered Dec 27, '11

Thanks to you both. The final result was: bcp "select * from tkcsdb.DBO.VP_TOTALS where ADJUSTEDAPPLYDATE between '2011-12-16' and '2011-12-31' " queryout C:\20111231Export.csv -Usa -Pabc123de -S Labeltime -T -c

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question