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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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