How will you get month number if month name is given?

Answer:

We can use the DATEPART function for accomplishing the task.This function is used to
return a single part of a date/time.

The general syntax is :

 DATEPART(datepart,date)

So if we specify the datepart as month, we will get the month component from this function.

e.g.

SELECT [MonthNumber] = DATEPART(mm,getdate())

will give the current month number since we are interested only in the month datepart (mm).

Now, given any month, we will first construct the first day of the month as under

Declare @monthname Varchar(20) = 'October'

Select CompleteDate = CAST(@monthname + ' 1, 1900' AS DATETIME)



/* Output */

CompleteDate

----------------------

1900-10-01 00:00:00.000

So, since we have now constructed the date, now we can easily apply the DATEPART function to obtain the month number

Declare @monthname Varchar(20) = 'October'

Select [MonthNumber] = DATEPART(mm,CAST(@monthname + ' 1, 1900' AS DATETIME))





/* Result */

[MonthNumber]

------------

10