Copy data from one column to another in the same table

Question

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

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 25-Aug-20 6:52am

Chris Slinko

Updated 18-May-22 23:37pm

Add a Solution

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?

1 solution

Add a Solution

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

Aravindba · Answer 1 · 2022-05-18T23:37:00

Solution 2

Hi Try this

SQL

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
Aravind

Posted 18-May-22 23:37pm

Aravindba

v2

Copy data from one column to another in the same table

1 solution

Solution 2

Add your solution here

Preview 0

Copy data from one column to another in the same table

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us