Change data type SQL 2012

>Conversion failed when converting date and/or time from character string.
from = Looks like a conversion attempt was made to convert a column from character to date/datetime, and this error usually occurs if there's a value somewhere that is not convertible to a date, such as 'banana', '2015-02-31', 'yesterday', or '2015-01-01 and 2015-01-02'.

SELECT CAST('banana' as date) 

Select allOpen in new window

So, you're going to have to find these values and either delete the rows or update with a valid date, then you can convert the column to a date.

Something like..

SELECT * FROM your_table WHERE ISDATE(the_column) = 0

Select allOpen in new window

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

SQL SERVER – Multiple ways to convert Datetime to Varchar

SQL SERVER 2012 -Conversion Function -TRY_CONVERT

ALTER TABLE table_name ALTER COLUMN column_name date NULL --or NOT NULL, whichever you need

Easiest way is:
1.  Create a new varchar column.
2.  Run an update statement to transfer DateTime to the new column

--
-- Convert string
--
UPDATE YOURTABLE set YOUR_COLUMN_NAME = LEFT(CONVERT(VARCHAR, YourDateColumn, 120), 10)

3. Test data integrety.
4. Drop the datetime column
5.  Rename the new column to the old column
6.  You just sucessful converted datetime to varchar :)

>>"when I perform my query"

Suggest you provide us with that query.

Then:
 Jim's correct, the error message is because some string cannot be changed into a date or datetime. Hence the error message does not align with your problem description.

Are you certain you know which column is giving you the problem?

I would suggest you also give us the DDL  (create table script) for the table being updated as well as your query.

Here is the query I am trying to run when I get the error message. Field_20 is the datetime data type.

SELECT TOP 1000 DocuCore_Repository_5.[document_id],[field_19],[field_108],[field_20],[field_109],[field_110],[field_111],[field_139],'Copy ' + DocuCore_Revisions.revision_pathtofile + ' ' + 'E:\Exports\' + field_19 + '_' + field_20 + '_' + field_108 + '_' + field_109 + '_' + field_110 + '_' + field_111 + '_' + field_139 + RIGHT(DocuCore_Revisions.revision_pathtofile,4)
  FROM [DocuCore_Repository_5], DocuCore_Revisions
  WHERE DocuCore_Repository_5.document_id = DocuCore_Revisions.document_id
      AND DocuCore_Repository_5.repository_id = DocuCore_Revisions.repository_id

If I remove field_20 from the query it runs fine. I need field_20 in my query results.

I have tried to use the TRY CONVERT and the multiple ways to change from date to varchar.
I did find several records, using the isdate(field_20) = 0, that had a null value in field_20 and I deleted those records.

Sorry in my query I posted field_108 and field_20 are in the wrong sequence. I transposed the numbers wrong just in this post. My query has it right on the sever.

>Field_20 is the datetime data type.
>field_19 + '_' + field_20 + '_' + field_10
For starters, you can't concatenate non-character values with character values, so give this a whirl..
field_19 + '_' + COALESCE(CAST(field_20 as varchar(max)), '')  + '_' + field_10

Looking at the SQL Server Data Type Conversion Table everything except for the bottom four can convert to varchar, so there must be more/other  things going on here..

...previous_part_of_query...
field_19 + '_' + ISNULL(CONVERT(varchar(8), field_20, 112) + '_' + REPLACE(CONVERT(varchar(5), field_20, 8), ':', ''), '19000101_0000') + '_' +
field_108 + '_' ...rest_of_query...