How can I get the month number (not month name) from a date in SQL Server?

You want DATEPART:

select datepart(mm, getdate())

Use datepart function with m extension.

SELECT DATEPART(m, getdate())

Use Datepart:

DATEPART(mm,getdate());

Use the month function - SELECT MONTH(GETDATE())