Convert month number to month names
-
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
-
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
-
If you want to use it against the column in the table try this:
select DATENAME(mm,ColumnName)
Thanks
Sreejith
-
"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.
-
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
-
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>
-
Cheers buddy
It works!!!
Thanks for help
-
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
-
or
DATENAME(month,DATEADD(month,[monthcolumn],-1))
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply