Extract Month Year as date data type

I'm not 100% sure I understand your question. the DATE data type contains day, month, and year information. It cannot contain anything other than that. Are you looking for the results in a string format? Are you trying to get the first or last day of any given month?

If you want a string that represents month year then try this:

PRINT RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8)

Select allOpen in new window

Just replace "GETDATE()" with whatever date value you need. You might also want to use a "SELECT" statement rather than just "PRINT".

Use FORMAT() (varchar result)

As per your other question (and the one before that)

select format(DT.CreateDate,'MMM yyyy') as [CreateMonthYear]

and

DATEFROMPARTS()
 
select datefromparts(year(cdate),month(cdate),01)  as CreateDate

which DOES give you a date datatype (with 01 as the day component) and IS very quick to build the date datatype

The resulting value you posted "Sep 2018" is not a date data type, so you have to manipulate the recognised DATE datatype YYYY-MM-DD to give you that result as a string.

There are other ways of achieving that result, but to get it into a display / presentation format then maybe we are doing it at the wrong time, and should be where ever the final (presentation) need is. (in which case use the datefromparts(), or, cast( yourdatecolumn as DATE) (though still has a day component), or,  dateadd(month,datediff(month,0,yourdatecolumn),0)  and so on.

What are we missing from your requirements ???

https://www.experts-exchange.com/questions/29122379/Merge-date-columns-display-month-as-MMM-and-sort-date-by-DESC.html?anchorAnswerId=42709998#a42709998
https://www.experts-exchange.com/questions/29121427/Count-of-Records-created-every-month.html?anchorAnswerId=42703899#a42703899

Seems pretty much the same question. This time without the data/table just the month+year

I read it as they really wanted a string in a specific format that looks like a date.

That is a common occurrence in the areas I normally stay in.

The way humans read or understand dates is by strings that represent those dates.  

strings (of the same date)
------------------------------------
1st of October 2018
Oct 1, 2018
1/10/18
2018-10-01
... & multiply these by different spoken languages

However, dates/times are stored in the database as NUMBERS, and then displayed to us humans as STRINGS

So,
 if you need a date in your presentation layer, avoid anything that returns a string representation i.e. do not use:
format(
convert(varchar