earwig75
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'),'')
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
Should probably check for a legit date : iif(isdate(createddate) = 1, format(createddate,'d','en
ASKER
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
Nope, cant see any reference to ms sql 2012
bit disappointed I didnt get an assist :(
bit disappointed I didnt get an assist :(
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...