Convert 3 character month abreviation to equivalent number?

_{Thursday, July 3, 2008 1:56 PM}

Hopefully I'm not asking too much of sql, but is there a way to convert a string value 'jan' to it's equivalent month number (ie. 1) with SQL?  I have a table I want to query that includes a varchar column filled with 3 letter month abreviations and I would like to return the numbers for them instead of the characters.  Thanks!

_{Thursday, July 3, 2008 2:11 PM ✅Answered}

You can adapt this:

create table #tmp(monthName3 varchar(3))

insert into #tmp values('Jan')

insert into #tmp values('Feb')

insert into #tmp values('Mar')

insert into #tmp values('Apr')

insert into #tmp values('May')

insert into #tmp values('Jun')

select month(cast(monthName3 + ' 1 2008'as datetime)) as MonthNumber from #tmp

drop table #tmp

Use your table and column in the select statement instead of #tmp and monthName3.

_{Thursday, July 3, 2008 2:05 PM}

as a quick fix you could add a case statement

Code Snippet

select case ShortMonth

when 'Jan' then 1

when 'Feb' then 2

-- add extra Months

end as MonthNum

from myTable

I

_{Thursday, July 3, 2008 2:11 PM | 1 vote}

A simpler version would be to

Code Snippet

SELECT MONTH('1 ' +'FEB' + CAST(YEAR(GETDATE()) AS CHAR(4)))

and replace the 'FEB' with a column

_{Thursday, July 3, 2008 2:18 PM}

You could use the "case" function.

select

case shortmonth

when 'Jan' then 1

when 'Feb' then 2

...

when 'Dec' then 12

else 0  -- unknown

end 

go

or may be something like:

Code Snippet

declare @t table (shortmonth char(3) not null)

declare @shortmonths as varchar(50)

set @shortmonths = ',Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,'

insert into @t(shortmonth) values('Jan')

insert into @t(shortmonth) values('Jan')

insert into @t(shortmonth) values('Dec')

insert into @t(shortmonth) values('Jul')

insert into @t(shortmonth) values('ula')

select

*

from

@t

outer apply

(

select

case

when @shortmonths like '%,' + [@t].shortmonth + ',%' then (patindex('%,' + [@t].shortmonth + ',%', @shortmonths) / 4) + 1

else null

end as month_number

) as m

GO

AMB

_{Thursday, July 3, 2008 2:25 PM}

Awesome thanks so much guys!

_{Thursday, July 3, 2008 2:31 PM}

Another approach is by using the month function and appending something like 3 2008 10:26AM'

run this

Code Snippet

declare @t table (shortmonth char(3) not null)
 

insert into @t(shortmonth) values('Jan')
insert into @t(shortmonth) values('Feb')
insert into @t(shortmonth) values('Dec')
insert into @t(shortmonth) values('Jul')
insert into @t(shortmonth) values('Mar')
insert into @t(shortmonth) values('Apr')
insert into @t(shortmonth) values('Oct')
insert into @t(shortmonth) values('May')

select shortmonth,month(shortmonth + '  3 2008 10:26AM') from @t

if you have invalid junk you can always use a case statement

Code Snippet

declare @t table (shortmonth char(3) not null)

insert into @t(shortmonth) values('Jan')
insert into @t(shortmonth) values('Feb')
insert into @t(shortmonth) values('Dec')
insert into @t(shortmonth) values('Jul')
insert into @t(shortmonth) values('Mar')
insert into @t(shortmonth) values('Apr')
insert into @t(shortmonth) values('Oct')
insert into @t(shortmonth) values('xxx')

select shortmonth,case isdate(shortmonth + '  3 2008 10:26AM') when 1 then
month(shortmonth + '  3 2008 10:26AM')
else null end from @t

Denis The SQL Menace

http://www.lessthandot.com/

http://sqlservercode.blogspot.com

http://sqlblog.com/blogs/denis_gobo/default.aspx

_{Thursday, July 3, 2008 2:35 PM | 1 vote}

I like that Alejandro.

In fact, I suspect that some variation of it will find itself into my 'tool kit'.

DECLARE @MyTable table

   (  RowID int IDENTITY,

      ShortMonth char(3)

   )

INSERT INTO @MyTable VALUES ( 'Aug' )

SELECT

   (( patindex( ('%,' + ShortMonth + '%' ),

                 ',Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec,' ) / 4 ) + 1 )

FROM @MyTable

And, this solution will not be affected by regional datetime settings as several of the other suggestions. It is totally independent from regional datetime settings and relies only on the string values provided.

This could work for most any set of known values...

_{Thursday, July 3, 2008 4:11 PM}

Arnie,

This solution is simplistic and elegant.  I will be adding this to my toolbelt too

_{Thursday, July 3, 2008 6:45 PM}

Arnie,

Glad you liked it.

Just keep in mind the possibility of a value that does not match any in the string, like the value I am using in my post "ula", for which it should return null or any predefined value not between 1 and 12. That is the reason why I added the "case" function, but I agree that if we can assure that the values in that column will match always (null value is ok because it yields null), then we could get rid of the "case" function.

AMB

_{Thursday, July 3, 2008 7:01 PM}

All of the functional solutions given here are very interesting, and get the job done. Another way to go woudl be to build a table with 12 rows, and include the different things you want to translate to, including the full spelled out month (sort of a trimmed down calendar table.)  It would give you a standard place to validate data (using a simple foreign key), will be a lot easier to work with, and probably faster if you start doing any grouping...

Just an idea.  If this is a quickie need, I probably wouldn't go to the difficulty either. 

_{Thursday, July 3, 2008 7:04 PM}

Hi Louis,

Agreed.

AMB

_{Friday, July 4, 2008 12:36 AM}

Acutally, I usually recommend having a full featured 'Calendar' table -which would solve this problem too.

_{Friday, July 4, 2008 12:40 AM}

I was going to suggest that, but I was thinking that it might be that they have a column that states the month for some other purpose and diidn't go there   Thanks for pointing this out Arnie!

I definitely agree that if the value in question represents the month of a date which could have been (or in fact is) stored in the same table, then a calendar table would be the way to go.

_{Friday, July 4, 2008 10:58 PM}

Thanks to everyone for the very educational discussion!  For what it's worth, the reason I needed this was because I was exporting info from an old application's database table that stored the month as the abbreviated string to a new database table that now stores it as a numerical value.

_{Friday, July 4, 2008 11:01 PM}

Thanks for the reply. It is always nice to hear that A. It works and B. commentary on why. Those of us who answer questions frequently love to find out the why, as we can help the people more, and it helps our education too