Getting only Month and Year from SQL DATE

SELECT convert(varchar(7), getdate(), 126) 

You might wanna check out this website: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Try this:

Portuguese

SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'pt-pt') + ' ' + convert(varchar(10),year(getdate()),100)

Result: maio 2019

English

SELECT format(dateadd(month, 0, getdate()), 'MMMM', 'en-US') + ' ' + convert(varchar(10),year(getdate()),100)

Result: May 2019

If you want in another language, change 'pt-pt' or 'en-US' to any of these in link

Try SELECT CONCAT(month(datefield), '.', year(datefield)) FROM YOURTABLE;

select CONCAT(MONTH(GETDATE()),'.',YEAR(GETDATE()))
Output: **5.2020**

select CONCAT(DATENAME(MONTH , GETDATE()),'.',YEAR(GETDATE()))
Output: **May.2020**

For reporting purposes I tend to use

CONCAT(YEAR([DateColumn]), RIGHT(CONCAT('00', MONTH([DateColumn])), 2))

You will lose the date type, since that will return (n)varchar. But with the leading zero this column is sortable (e.g. '202112' > '202102'). You could also add a seperator between year and month like

CONCAT(YEAR([DateColumn]), '-', RIGHT(CONCAT('00', MONTH([DateColumn])), 2))

returning somethink like '2020-08' (still sortable).

For MySQL, this works pretty well:

DATE_FORMAT(<your-date>, '%Y-%m %M')

It goes into the SELECT statement. Here's the output:

2014-03 March

For more information about DATE_FORMAT, check out the documentation: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

I had a specific requirement to do something similar where it would show month-year which can be done by the following:

SELECT DATENAME(month, GETDATE()) + '-' + CAST(YEAR(GETDATE()) AS nvarchar) AS 'Month-Year'

In my particular case, I needed to have it down to the 3 letter month abreviation with a 2 digit year, looking something like this: SELECT LEFT(DATENAME(month, GETDATE()), 3) + '-' + CAST(RIGHT(YEAR(GETDATE()),2) AS nvarchar(2)) AS 'Month-Year'

Another simple answer is to remove the number of day from the date

Here how to do it in BigQuery

SELECT DATE_ADD(<date_field>, INTERVAL 1 - extract(day from <date_field>) DAY) as year_month_date

An exemple :

SELECT DATE_ADD(date '2021-03-25', INTERVAL 1 - extract(day from date '2021-03-25') DAY) as year_month_date

This return 2021-03-01

For result: "YYYY-MM"

SELECT cast(YEAR(<DateColumn>) as varchar) + '-' + cast(Month(<DateColumn>) as varchar)

Get Month & Year From Date

DECLARE @lcMonth nvarchar(10)
DECLARE @lcYear nvarchar(10)

SET @lcYear=(SELECT  DATEPART(YEAR,@Date))
SET @lcMonth=(SELECT  DATEPART(MONTH,@Date))

Query :- Select datename(m,GETDATE())+'-'+cast(datepart(yyyy,GETDATE()) as varchar) as FieldName

Output :- January-2019

general datefield we can use

datename(m,<DateField>)+' '+cast(datepart(yyyy,<DateField>) as varchar) as FieldName

SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-')

Output: Mar-2019

SELECT * FROM demo 
    WHERE attendance_year_month < SUBSTRING(CURRENT_TIMESTAMP,1,11) 
          AND
          attendance_year_month >= DATE_SUB(SUBSTRING(CURRENT_TIMESTAMP,1,11), INTERVAL 6 MONTH)

Function "Format" is a convenient and flexible, but there is a performance concern, more details

If you are not very serious about formatting, grab year month by converting it as a string, the running time is acceptable as well

SELECT substring(<DateColumn> as varchar) ,0, 8 ) as YearMonth