sql-servert-sqldatetimesql-date-functionssqldatetime

how to convert month date in a text column into datetime

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.

Solution

  • 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>