How to get the name of day of the week of the first day of the month?

You can also use FORMAT as well. See:

DECLARE @Data DATETIME = '2017-12-01 00:00:00'
SELECT FORMAT(@Data, 'dddd') AS dia_semana

The return:

dia_semana
-------------
Friday

The format dddd refers to the day of the week. The DATENAME , quoted in the response from the @ MarconcilioSouza can be used to get the name of the month and year as well. The format I used is to get the day of the week and only.

• MSDN : Date and Time Data Types and Functions
• MSDN: FORMAT (Transact-SQL)
• MSDN: DATENAME (Transct-SQL)

If you already know the date by using:

SELECT DAYNAME('2017-10-16');

If you need the date in Portuguese and your bank has not configured you can use it before the query like this:

SET lc_time_names = 'pt_BR';

Just take care because if user, SET will have to return to the default language after returning the query.

If you want to leave the month flexible, you can leave it in a variable, part of the example:

declare @mes varchar(2) = '12'
select DATENAME(weekday, '2017-' + @mes + '-01') as dia_semana

You can mount the query as follows.

SELECT (DATENAME(dw, CAST('12' AS VARCHAR) + '/' + CAST('1' AS VARCHAR)  + '/'  + CAST(DATEPART(yy, getdate()) AS VARCHAR)) )

In this case you can also use this:

SET @var := year(now());
Select @var, DAYNAME(CONCAT_WS('-', @var,'12-01'));