Click here to Skip to main content
14,665,550 members
Rate this:
Please Sign up or sign in to 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

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
0x01AA 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.
0x01AA 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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100