CODEWITHSUNDEEP
sqlsql-server
brock434
brock434

Reputation: 3

Update column with concatenated date (SQL Server)

I'm trying to update a column with the concatenated and converted results of two other columns in order to create a column with a date field. The SELECT statement returns the values I want to Update with, but I'm missing something (probably simple) on the update. It won't execute because

"the subquery returns more than one value".

However, I don't want to update with the same value for each row, but rather the concatenated result for each row.

What am I missing?

UPDATE myTable
SET myDate = 
(
SELECT 
CONVERT (Date,(CONVERT (NVarchar, CreatedYear) + '-' + CONVERT (NVarchar, CreatedMonth) + '-' + '01') ,0)

FROM myTable
)

Upvotes: 0

Views: 121

Answers (2)

Eric H
Eric H

Reputation: 1789

This is a bit more readable in my opinion:

UPDATE dbo.tblControlMaster SET AuditAddDate = CAST(CreatedYear AS NVARCHAR(4)) + '-' + CAST(CreatedMonth AS NVARCHAR(2)) + '-' + '01'

Upvotes: 0

Steven Schroeder
Steven Schroeder

Reputation: 6194

I believe you have an extra SELECT that is not required. Please try:

UPDATE myTable 
SET myDate =  
CONVERT (Date,(CONVERT (NVarchar, CreatedYear) + '-' + CONVERT (NVarchar, CreatedMonth) + '-' + '01') ,0) 

FROM myTable

Upvotes: 1

Related Questions