Change data type SQL 2012

Hello Experts,

I have a database with 14 tables each table containing the same amount of fields. I mistakenly made 1 field a datatime type and would like to change it to a straight date type or varchar type. I am getting this error message when I perform my query.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

How can I change this type or work around it.
LVL 9
samiam41Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>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) 

Open 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

Open in new window

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

SQL SERVER – Multiple ways to convert Datetime to Varchar

SQL SERVER 2012 -Conversion Function -TRY_CONVERT
Scott PletcherSenior DBACommented:
ALTER TABLE table_name ALTER COLUMN column_name date NULL --or NOT NULL, whichever you need
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

dameyCommented:
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 :)
PortletPaulEE Topic AdvisorCommented:
>>"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.
samiam41Author Commented:
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.
samiam41Author Commented:
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.
Jim HornMicrosoft SQL Server Data DudeCommented:
>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..
Scott PletcherSenior DBACommented:
...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...
PortletPaulEE Topic AdvisorCommented:
IF you concatenate columns together and any of these happen to be null the result is NULL.

What do you actually want in the concatenation from [Field_20]?

e.g. do you want it as YYYY-MM-DD? DD-MM-YYYY? MM-DD-YYYY?
you can control the "style" of a datetime output using CONVERT()
or from SQL 2012 onward you can use FORMAT()
see: SQL Server Date Styles (formats) using CONVERT()

Plus: What do you want if [Field_20] is null?


for example, if you want a date in the style of YYYY-MM-DD , and just " - " if [Field_20] is null
then you could use one of these options inside the concatenation where you have Field_20 now

...
+ ISNULL( CONVERT(VARCHAR(10), [Field_20] ,121)  , ' - ') -->> any SQL Server version

              OR
...
+ ISNULL( FORMAT([Field_20], 'yyyy-M-d') , ' - ') -->> SQL Serer 2012 onward

So. You need to know what date/time format you expect, and what to substitute if that field is null.

----------------
By the way, it really does help if you format queries for ease of reading. Whilst it makes no difference to the SQL parser it really does to us humans. For long concatenations I like to spread them out.
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
      + '_'
      + ISNULL( CONVERT(VARCHAR(10), [Field_20] ,121)  , ' - ')
      + '_'
      + field_108
      + '_'
      + field_109
      + '_'
      + field_110
      + '_'
      + field_111
      + '_'
      + field_139
      + RIGHT(DocuCore_Revisions.revision_pathtofile, 4)
FROM [DocuCore_Repository_5]
INNER JOIN DocuCore_Revisions
      ON DocuCore_Repository_5.document_id = DocuCore_Revisions.document_id
      AND DocuCore_Repository_5.repository_id = DocuCore_Revisions.repository_id

Open in new window

and I have also used ANSI join syntax which I encourage you to do.

Note.
Scott Pletcher has proposed a very specific format for field_20 using style numbers 112 and then style 8 with colons removed which results in a date/time pattern as follows:

YYYYMMDD_hhmm

try it with this:
declare @field_20 as datetime = getdate()

SELECT
      ISNULL(CONVERT(varchar(8), @field_20, 112) 
       + '_' 
       + REPLACE(CONVERT(varchar(5), @field_20, 8), ':', ''), '19000101_0000') 
     , getdate()  

Open in new window


then if field_20 is null he proposes using the constant string '19000101_0000'

IF you have SQL 2012 or later the equivalent is:

...
+ ISNULL( FORMAT(field_20,'yyyyMMdd_hhmm') ,'19000101_0000')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.