I have a table with a column "VALUATION DATE NUM", which stores integer in '20210331' format. I have another column in the same table "VALUATION DATE" which is in date format, however is blank for now. I want to use the "VALUATION DATE NUM" column to update "VALUATION DATE" column in date format.

Using the query "UPDATE SPRD_MGMT_INP_FUND_VALUES SET "VALUATION DATE" = TO_DATE("VALUATION DATE NUM",'YYYY/MM/DD');" is giving an error "SQL compilation error: invalid type [TO_DATE(SPRD_MGMT_INP_FUND_VALUES."VALUATION DATE NUM", 'YYYY/MM/DD')] for parameter 'TO_DATE'" Please help

CodePudding user response：

Using spaces in column names is not a good practice. Do you need something like this?

update yourtable SET "VALUATION DATE" = TO_DATE( "VALUATION DATE NUM"::VARCHAR,'YYYYMMDD' ) where "VALUATION DATE" is NULL; 

Note: Because you said blank values I added the WHERE condition.