Get Month Name And Number In SQL SERVER

  1. SELECT number,  
  2. DATENAME(MONTH, CAST(year(getdate()) as varchar(4)) +'-' + CAST(number as varchar(2)) + '-1') monthname  
  3. FROM master..spt_values  
  4. WHERE Type = 'P' and number between 1 and 12  
  5. ORDER BY Number  
OUTPUT

Number   monthname

1                January
2                February
3                March
4                April
5                May
6                June
7                July
8                August
9                September
10              October
11              November
12              December