Convert month number to month names

Question

Post reply

Convert month number to month names

Nuts

SSCrazy

Points: 2547
More actions
September 20, 2006 at 2:18 pm

#114582

Hi guys ! i have a column in my table which has got month numbers. I want to convert these numbers to month names.How do i do that?

Cheers
Mita

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Add the month number to an arbitrary date and use the DATENAME function.

DECLARE @date datetime

DECLARE @MonthColumns table (MonthNumber int)

insert into @MonthColumns

select 1 union all

select 2 union all

select 3 union all

select 4 union all

select 5 union all

select 6 union all

select 7 union all

select 8 union all

select 9 union all

select 10 union all

select 11 union all

select 12

SET @Date = '01/01/01'

SELECT DATENAME(mm,DATEADD(mm,(MonthNumber - 1),@date))

FROM @MonthColumns

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

If you want to use it against the column in the table try this:

select DATENAME(mm,ColumnName)

Thanks

Sreejith

John Rowan SSC Guru Points: 56440 More actions · Answer 3

"If you want to use it against the column in the table try this:select DATENAME(mm,ColumnName)"

This will not work as the original post says that the column only holds the month number and not the full date. This is why I suggested using DATEADD with an arbitrary date.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Nuts SSCrazy Points: 2547 More actions · Answer 4

ya because my column just has month numbers .there is no date in it.

I just tried the query which u suggeste. It gave me the names of all the months.

So now what I have to do?

Sorry for my lack of knowledge.I am still learning

John Rowan SSC Guru Points: 56440 More actions · Answer 5

Substitute your table name for the @MonthColumns table variable and your column name for MonthNumber. For example:

DECLARE @date datetime

SET @Date = '01/01/01'

SELECT DATENAME(mm,DATEADD(mm,(<YOUR COLUMN> - 1),@date))

FROM <YOUR TABLE>

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Nuts SSCrazy Points: 2547 More actions · Answer 6

Nuts

SSCrazy

Points: 2547

September 20, 2006 at 4:06 pm

#661594

Cheers buddy

It works!!!

Thanks for help

Martin Sloan SSC Enthusiast Points: 158 More actions · Answer 7

The posted solutions seem overly complicated to me. I'd do something like this:

select datepart(mm, convert(datetime, convert(varchar(2), MonthNumberColumn) + '/20/2006')) from MonthNumberTable

The day and year in the string ('/20/2006') don't really matter, just make the day number > 12 so the resulting date is unambiguous.

Good luck

David Burrows SSC Guru Points: 65142 More actions · Answer 8

or

DATENAME(month,DATEADD(month,[monthcolumn],-1))

Far away is close at hand in the images of elsewhere.
Anon.