June 28, 2006 at 12:39 pm
I am getting strange output from BCP and it is creating a problem with Excel.
BCP Format File Contents
8.0
5
1 SQLCHAR 0 25 , 1 Last Name
2 SQLCHAR 0 25 , 2 First Name
3 SQLDATETIME 0 08 , 4 Beginning Date
4 SQLDATETIME 0 08 , 5 Adj Begining date
5 SQLCHAR 0 02 , 3 Months Employed
BCP Execution File
bcp "SELECT * FROM FASTECHTIME..EmpEvals WHERE eval_months = 3 ORDER BY last_name, first_name" queryout EmpEvalsDue3.txt /STime /Uxxxxx /Pxxxxxxxx /fEmpEvalsDue.fmt /oEmpEvalsDue3.msg /eEmpEvalsDue3.err /t, /c /q /k
The Output
firstname,lastname,2006-03-06 00:00:00.000,,,3
As you can see the 2 datetime fields are together and I have 2 null fields. When the date fields show up in Excel, it displays as 00:00:00.000 with no dispay of the good date.
Is there a way to fix this? I am not a BCP guru so I have ben trying everything possible with no results. Help is GREATLY appreciatd.
Mike
June 28, 2006 at 1:00 pm
I think this is an Excel issue and not a BCP issue. Once you open the Excel file, right-click on the datetime column and select format cells. There select the format you want to appear. There are some date/time formats under Date and Time, but there are also some more that resemble the raw data more under Custom.
June 28, 2006 at 1:14 pm
I think it is a BCP issue because of the way the output is presented. There should be a comma between the 2 date fields instead of just before the last field. If that can be fixed then Excel will show it properly. Look at the format file and the output file to see what I mean. For some reason BCP is putting the 2 datetime fields in 1 field instead of 2.
Mike
June 28, 2006 at 1:14 pm
Hmmmm.... I only see one datetime column in the output you provided, and it's longer than the 8 characters you provided for in the format file. My guess is that you need to add a column list to the query rather than 'select *', convert the dates to the 8 character format you want, and check for NULL dates.
June 28, 2006 at 1:20 pm
I did
bcp "SELECT last_name, first_name, begin_dt, adj_beg_dt, eval_months FROM FASTECHTIME..EmpEvals WHERE eval_months = 3 ORDER BY last_name, first_name" queryout EmpEvalsDue3.txt /STime /Uxxxxx /Pxxxxxxxx /fEmpEvalsDue.fmt /oEmpEvalsDue3.msg /eEmpEvalsDue3.err /t, /c /q /k
and the output was the same.
Mike
June 28, 2006 at 1:32 pm
Mike,
The value in your output '2006-03-06 00:00:00.000' is one datetime (yyyy-mm-dd hh:mm:ss.hhh) value, for the first date column you selected, so your output is exactly as it should look if the second date is NULL.
To get the dates to be 8 characters long, you need to use a CONVERT function. You also need to use an ISNULL function to check for NULL dates if you want something other than ',,' in the output.
SELECT last_name, first_name, ISNULL(CONVERT(varchar(8),begin_dt,112),'20000101'), ISNULL(CONVERT(varchar(8),adj_beg_dt,112),'20000101'), eval_months FROM FASTECHTIME..EmpEvals WHERE eval_months = 3 ORDER BY last_name, first_name
in this case, the dates default to 20000101 if no date is present.
June 28, 2006 at 1:58 pm
Opps! DUH! I did not catch that. You are absolutely correct. So it is working okay.
Thanks for the enlightenment and the CONVERT functions. It works great now.
Mike
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply