Click here to Skip to main content
15,116,516 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

UPDATE VISION_SALEHIST4
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
SELECT FORMAT(SALEDATE, 'MM/dd/yyyy', 'en-US') AS [MM/DD/YYYY]
FROM VISION_SALEHIST4;"

I got error messages from all.
Posted
Comments
[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?

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