I need to take a month and day field in a table, and update a new date field, based upon the month and year. I tried this query, which says it updates the records, but none of the date fields are updated. Can someone tell me what is wrong with the query or suggest a better one?
UPDATE dbo.pd_products SET launch_date =
(select cast( B.launch_year + ‘-‘ + B.launch_month + ‘-‘ + ‘1’ AS datetime)
from dbo.pd_products A join dbo.pd_products B
on A.product_key = B.product_key
and A.launch_date = B.launch_date)
where substring(launch_year,1,1) != ‘ ‘
and substring(launch_month,1,1) != ‘ ‘
I tried the query in SQL Server Query Analyzer. While it says it updated the rows (all 500), all the launch_date fields were null. Is this the query or how the data fields are defined? Here are the particulars:
Column_name data_type type_name Column_size
launch_month 12 varchar 2
launch_year 12 varchar 4
launch_date 93 datetime 23
