Click here to Skip to main content
15,794,475 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I'm trying to copy data from 1 column to another column in the same table. I want to copy the data that is in the colmn named saledate_mmddyyyy to the column named SALEDATE. The SALEDATE column is date and time where as the saledate_mmddyyyy column is just the date. I'm getting this error message " Msg 271, Level 16, State 1, Line 1
The column "saledate_mmddyyyy" cannot be modified because it is either a computed column or is the result of a UNION operator."

The code is below

SET saledate_mmddyyyy = SALEDATE

What I have tried:

I've tried this code "alter table VISION_SALEHIST4 alter column saledate date" and I've tried this query "USE taxpayer

I got error messages from all.
Updated 19-May-22 0:37am
[no name] 25-Aug-20 13:24pm    
First your message is confusing.
Quote: " I want to copy the data that is in the colmn named saledate_mmddyyyy to the column named SALEDATE"
But in your SQL you try the oposite: UPDATE VISION_SALEHIST4 SET saledate_mmddyyyy = SALEDATE

Looks like 'saledate_mmddyyyy' is the date in text, so you need to convert it.

Finally you need to do something like this:
UPDATE VISION_SALEHIST4 SET SALEDATE= CONVERT(DATETIME, (SUBSTRING(saledate_mmddyyyy, 5, 4) + '.' + SUBSTRING(saledate_mmddyyyy, 1, 2) + '.' + SUBSTRING(saledate_mmddyyyy, 3, 2)), 101)
Chris Slinko 25-Aug-20 13:32pm    
Thanks I just tryed it an it did work this is the error message that I got. "Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string."

I just want to copy and past. Copy the data from salesdate_mmddyyyy and paste it into the column named SALEDATE. I guess I didn't get the right sytax for the query to work.
[no name] 25-Aug-20 13:48pm    
Maybe adding a where UPDATE .... WHERE NOT saledate_mmddyyyy IS NULL would help?

1 solution

Hi Try this
UPDATE  VISION_SALEHIST4 SET SALEDATE=Convert(varchar(20), saledate_mmddyyyy ,120) 

From above query u will update column SALEDATE with date and time, but time will be 00:00:00

Thanks and Regards
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900