SQL- How to convert a YYYYMM number to a date

Question

So I'm pretty new to SQL and I have a column full of numbers that list year and month in YYYYMM format (i.e. 201607 for July 2016). What I'd like to know is if I can convert this into proper date format within SQL. I've done a fair bit of research and I've seen a lot of answers regarding converting YYYYYMMDD form to a date, but not much with only the 6 characters I've got.

Anybody got any ideas?

Answer 1

Assuming that every one would be the first of the month (i.e. 201807 would be 2018-07-01):

CONVERT(date, YourColumn + '01')

If you require a different day in that month, you'll need to provide more detail.

Answer 2

You can do like this if you want:

   select *, FORMAT(DATEFROMPARTS(1900, right(YearMonthKey,2), 1), 'MMMM', 'en-US') +' '+ Left(YearMonthKey,4)  as MonthYearName from (
select 201709 as YearMonthKey
)x

Result