CODEWITHSUNDEEP
sql-server
Gary Barrett
Gary Barrett

Reputation: 2028

Convert month name to month number in SQL Server

In T-SQL what is the best way to convert a month name into a number?

E.g:

'January' -> 1
'February' -> 2
'March' -> 3

Etc.

Are there any built in functions that can do this?

Upvotes: 30

Views: 192542

Answers (14)

johnnn
johnnn

Reputation: 9

try this

SELECT EXTRACT(MONTH FROM TO_DATE(month_added, 'Month')) AS month_number

Upvotes: -1

Alp teja
Alp teja

Reputation: 301

Its quit simple, Take the first 3 digits of the month name and use this formula.

Select charindex('DEC','JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC')/4+1

Upvotes: 18

Zed Fang
Zed Fang

Reputation: 883

You can try sth like this, if you have month_name which is string datetype.After converting, you can feel free to order by Month.

For example, your table like this: 

 month
 Dec
 Jan
 Feb
 Nov
 Mar
  .
  .
  .

My syntax is:

 Month(cast(month+'1 2016' as datetime))

Upvotes: 1

Rodger Svovah
Rodger Svovah

Reputation: 51

I know this may be a bit too late but the most efficient way of doing this through a CTE as follows:

 WITH Months AS
    (
       SELECT 1 x
       UNION all
       SELECT x + 1
       FROM Months
       WHERE x < 12

     )
     SELECT x AS MonthNumber, DateName( month , DateAdd( month , x , -1 ))            AS MonthName FROM Months

Upvotes: 0

Punit Singh
Punit Singh

Reputation: 11

You can use below code

DECLARE @T TABLE ([Month] VARCHAR(20))
INSERT INTO @T
SELECT 'January'
UNION
SELECT 'February'
UNION
SELECT 'March'`

SELECT MONTH('01-' + [Month] + '-2010') As MonthNumeric,[Month] FROM @T
ORDER BY MonthNumeric

Upvotes: 1

user3615318
user3615318

Reputation: 125

I think you may even have a separate table like a monthdetails (Monthno int, monthnames char(15)) and include values:

1 January
2 February

.... and so on, and then join this table with your existing table in the monthnames column

SELECT t1.*,t2.Monthno from table1 
left outer join monthdetails t2
on t1.monthname=t2.monthnames
order by t2.Monthno

Upvotes: 1

Shivakumar Durg
Shivakumar Durg

Reputation: 71

SELECT DATEPART(MM,'january '+'01 1900')
SELECT MONTH('january ' + '01 1900')
SELECT month(dateadd(month,DATEDIFF(month,0,'january 01 2015'),0))

Upvotes: 7

RedMartian
RedMartian

Reputation: 11

I recently had a similar experience (sql server 2012). I did not have the luxury of controlling the input, I just had a requirement to report on it. Luckily the dates were entered with leading 3 character alpha month abbreviations, so this made it simple & quick: 

TRY_CONVERT(DATETIME,REPLACE(obs.DateValueText,SUBSTRING(obs.DateValueText,1,3),CHARINDEX(SUBSTRING(obs.DateValueText,1,3),'...JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC')/4))

It worked for 12 hour:
Feb-14-2015 5:00:00 PM 2015-02-14 17:00:00.000
and 24 hour times:
Sep-27-2013 22:45 2013-09-27 22:45:00.000

(thanks ryanyuyu)

Upvotes: 1

Sunil Johnson
Sunil Johnson

Reputation: 1169

How about this:

SELECT MONTH('March' + ' 1 2014')

Would return 3.

Upvotes: 24

Joro
Joro

Reputation: 41

You can create a function and then refer to it in the select statement. The function may look similar to this:

if OBJECT_ID('fn_month_name_to_number', 'IF') is not null
drop function fn_month_name_to_number
go
create function fn_month_name_to_number (@monthname varchar(25))
returns int as
begin
declare @monthno as int;
select @monthno =
case @monthname
when 'January' then 1
when 'February' then 2
when 'March' then 3
when 'April' then 4
when 'May' then 5
when 'June' then 6
when 'July' then 7
when 'August' then 8
when 'September' then 9
when 'October' then 10
when 'November' then 11
when 'December' then 12
end
return @monthno
end

Then you can query it.

select fn_month_name_to_number ('February') as month_no

This query will return 2 as month number. You can pass values from a column as parameters to the function.

select fn_month_name_to_number (*columnname*) as month_no from *tablename*

Have a good day!

Upvotes: 4

Graham Baitson
Graham Baitson

Reputation: 600

You can do it this way, if you have the date (e.g. SubmittedDate)

DATENAME(MONTH,DATEADD(MONTH, MONTH(SubmittedDate) - 1, 0)) AS ColumnDisplayMonth

Or you can do it this way, if you have the month as an int

DATENAME(MONTH,DATEADD(MONTH, @monthInt - 1, 0)) AS ColumnDisplayMonth

Upvotes: 0

bobthecoder
bobthecoder

Reputation: 1

select  Convert(datetime, '01 ' +  Replace('OCT-12', '-', ' '),6)

Upvotes: -3

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

There is no built in function for this.

You could use a CASE statement:

CASE WHEN MonthName= 'January' THEN 1
     WHEN MonthName = 'February' THEN 2
     ...
     WHEN MonthName = 'December' TNEN 12
END AS MonthNumber

or create a lookup table to join against

CREATE TABLE Months (
    MonthName VARCHAR(20),
    MonthNumber INT
);

INSERT INTO Months
    (MonthName, MonthNumber)
    SELECT 'January', 1
    UNION ALL
    SELECT 'February', 2
    UNION ALL
    ...
    SELECT 'December', 12;

SELECT t.MonthName, m.MonthNumber
    FROM YourTable t
        INNER JOIN Months m
            ON t.MonthName = m.MonthName;

Upvotes: 2

Ric
Ric

Reputation: 13248

How about this?

select DATEPART(MM,'january 01 2011') -- returns 1
select DATEPART(MM,'march 01 2011')  -- returns 3
select DATEPART(MM,'august 01 2011') -- returns 8

Upvotes: 42

Related Questions