converting Year and Month varchar to date
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname 2013 11 ACB 2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date 2013 11 ACB 2013-11-01 2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
Please let me know
Convert your month and year columns to strings concatenated with '-'s and add '-01' to the end. Then convert to a date.
UPDATE t SET t.[Date] = convert(date, convert(varchar(4), t.[Year]) + '-' + convert(varchar(2), t.[Month]) + '-01') FROM Table t
This will create a date for all records in your table.
It should just be a matter of concatenating things if I understand the problem.
update table1 set [date] = convert(date, month + '-01-' + year)
SET [Date] = DATEFROMPARTS([Year],[Month],1)