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

 Posted by Niladri.Biswas on 7/19/2012 | Category: Sql Server Interview questions | Views: 2156 | Points: 40

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 :

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


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 */
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 */

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response