how to convert month date in a text column into datetime-CodePudding

I have a table containing two columns like this:

Month_Date  Year
Dec 31      2018
May 01      2020
Jun 05      2021
Jan 18      2022
Jul 19      2019

I hope to combine the Month_date and year in the same row and put it in a new column as a datetime format column. Could anyone help, please? I tried to convert the first column to a valid date, but failed because it doesn't show a complete month name.

CodePudding user response：

You can try below approach.

Approach1:

select convert(datetime, [Month_Date]    ', '   cast([Year] as varchar(4)), 107) from <TableName>

Approach2:

select cast(right(month_date,2) '-' left(month_date,3) '-' cast([Year] as varchar(4)) as date) from <TableName>