I am having trouble writing a query to update a column with data from another column.

I am trying to write a query to update a "Comment" field on some records. Part of the Comment field needs to include a date from another column in each record.

For example, for the record below, I'd like to write a query that changes the comment to: "This record was created on [CreatedDate] and passed inspection."

Can someone assist?

ID   |     Comment     |     CreatedDate
-------------------------------------------------------
1     |                           |     1/18/2018
earwig75Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
update TableName
set Comment = 'This record was created on ' + CONVERT(nvarchar(30), CreatedDate, 101) + ' and passed inspection.'
where ID = 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
You can, do it directly...
update t set comment = 'This record was created on '+ format(CreatedDate,'MM\/dd\/yyyy')+' and passed inspection.'
from yourtable t

Open in new window

Few different ways to format the date, and probably want to check if it is a legit date by using isdate(). But the basic structure is a direct update - it knows what row it is on and you can draw on information in that row...
Mark WillsTopic AdvisorCommented:
What problem are you having ?

What version of SQL Server are you on ?

format() is available from sql2012 onwards, otherwise will need to use convert(varchar(10, createddate,101)  
where 101 is the style code https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles

if on sql2012 or more recent, can also use format(createddate,'d','en-US')

Should probably check for a legit date :  iif(isdate(createddate) = 1, format(createddate,'d','en-US'),'')
earwig75Author Commented:
Thank you, this works great. I had a small typo in my convert. I did note on the question tags it was MS SQL 2012
Mark WillsTopic AdvisorCommented:
Nope, cant see any reference to ms sql 2012

bit disappointed I didnt get an assist :(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.