number to monthname - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

number to monthname

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-05-02 : 02:22:07

Hi All !
How could I convert number to the name of the month. Sample 1 for january, 2 for februaly, and so on...

I have a proc.
DECLARE @monthInt int
SET @monthInt = 5

SELECT .....dont know how to do this...

Thanks!

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 02:24:19

Use CASE statement!

DECLARE @monthInt int
SET @monthInt = 5

Select
	CASE @monthInt
		When 1 then 'January'
		When 2 then 'February'
		When 3 then 'March'
		When 4 then 'April'
		When 5 then 'May'
		When 6 then 'June'
		When 7 then 'July'
		When 8 then 'August'
		When 9 then 'September'
		When 10 then 'October'
		When 11 then 'November'
		When 12 then 'December'
	End as MonthName

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-05-02 : 02:26:08

so 12 cases ? too long... Is there any other way to convert directly to MONTH ?

thanks!

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 02:29:03

Another simple way:

DECLARE
	@monthInt int

SET @monthInt = 5

select datename(month,dateadd(month, @monthInt - 1, 0)) as MonthName

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-05-02 : 02:30:46

Oh ! This is what I need. Short one...

Thanks.

-Ron-

"If you can only access one site on the Internet, make it SQLTeam!"

MissaLissa
Starting Member

1 Post

Posted - 2011-05-05 : 12:13:06

Here's the shortmonth

DECLARE
@monthInt int

SET @monthInt = 5

select left(datename(month,dateadd(month, @monthInt - 1, 0)),3)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-05 : 12:55:15

quote:
so 12 cases ? too long...

The code may be longer but the CASE expression performs at least 30% faster than the Dateadd version.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 13:39:30

If you want to write obscure code that no one else can figure out, you could even use this:

declare @monthInt int;
set @monthInt = 7;

select DATENAME(month,29*@monthInt);

It may still be slower than the case statement.

Sunita.

PS: No, I NEVER write obscure code, this was only of academic interest, or if you prefer, an example of what NOT to do!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 14:29:52

An even better example of what not to do!

declare @monthInt int
set @monthInt = 8

select parsename(replace(convert(varchar(11),dateadd(month,@monthInt,-1),100),' ','.'),3)

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 08:43:22

Yeah, sure, but mine is worse

. I have the magic number 29 init. You have no such magic numbers!!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-06 : 08:55:18

Plus 29 is a prime number! But I do have 3 and 11, which are prime. I wonder if there's a wors way to do this?

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 09:35:28

quote:
Originally posted by jimf
I wonder if there's a wors way to do this?

Did I hear someone call me?

DECLARE @monthInt INT 
SET @monthInt = 8

;WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<12),
	m(m) AS (SELECT DATENAME(MONTH, DATEADD(MONTH,n-1,0)) FROM n),
	fm(fm) AS (SELECT CAST(m AS CHAR(9)) FROM m),
	xm(xm) AS (SELECT ' '+fm FROM fm FOR XML PATH(''))
SELECT RTRIM(LTRIM(SUBSTRING(xm,((@monthInt-1)*10)+1,10))) FROM xm

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-06 : 10:07:16

That's truly awful! If I could code like that I'd have lifetime job security. I'd be like a COBOL programmer that has to be kept around since they're the only ones who know where the code is or what it does.

Jim

Everyday I learn something that somebody else already knew

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-05-06 : 10:11:24

quote:

Originally posted by robvolk

quote:
Originally posted by jimf
I wonder if there's a wors way to do this?

Did I hear someone call me?

DECLARE @monthInt INT 
SET @monthInt = 8

;WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<12),
	m(m) AS (SELECT DATENAME(MONTH, DATEADD(MONTH,n-1,0)) FROM n),
	fm(fm) AS (SELECT CAST(m AS CHAR(9)) FROM m),
	xm(xm) AS (SELECT ' '+fm FROM fm FOR XML PATH(''))
SELECT RTRIM(LTRIM(SUBSTRING(xm,((@monthInt-1)*10)+1,10))) FROM xm

I'm suing SQL Team for my bleeding eye problem now.

Hey, it compiles.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-06 : 10:14:14

If that hurt your eyes, then don't look at this one: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159724

Mine's not even the worst one there.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-07 : 14:01:04

You guys have too much time to waste...

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-07 : 19:40:15

Mirko! This is academic research!! In fact, I was going to propose that we try to publish the findings in a refereed journal such as Annals of Database Technology. I just have to come up with a query worse than Rob's query, but I am blanking out at the moment!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-07 : 21:47:15

quote:
I just have to come up with a query worse than Rob's query

Oh I was just teasing before, I've only just started:

DECLARE @monthInt INT 
SET @monthInt = 8

;WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<100),
	ms(m) as (select ','+months+',' from syslanguages where langid = @@LANGID),
	m(r,m) as (select row_number() over (order by m), SUBSTRING(m,n,charindex(',',m,n)-n) 
	from ms cross join n 
	where n<=LEN(m) and SUBSTRING(m,n-1,1)=',')
select m from m where r=@monthInt

I'm trying to figure out a way to incorporate xp_cmdshell, bcp, and changing the system clock, but I welcome anyone to beat me to it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-08 : 10:34:01

I don't think I can do any better!! I was thinking of using a cursor, but that didn't seem to pan out well.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-08 : 12:14:10

Well if it is for scientific purpose...

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-08 : 12:46:16

quote:
Originally posted by sunitabeck
I was thinking of using a cursor, but that didn't seem to pan out well.

OF COURSE! THAT'S EXACTLY WHAT MY CODE NEEDED!

DECLARE @monthInt INT, @monthFetch int, @monthNameFetch varchar(50)
SET @monthInt = 8

DECLARE curs CURSOR GLOBAL SCROLL DYNAMIC OPTIMISTIC FOR
WITH	n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n<100),
	ms(m) AS (SELECT ','+months+',' FROM syslanguages WHERE langid = @@LANGID),
	m(r,m) AS (SELECT ROW_NUMBER() OVER (ORDER BY m), SUBSTRING(m,n,charindex(',',m,n)-n) 
	FROM ms CROSS JOIN n 
	WHERE n<=LEN(m) AND SUBSTRING(m,n-1,1)=',')
SELECT * FROM m FOR UPDATE

OPEN curs
FETCH NEXT FROM curs INTO @monthFetch, @monthNameFetch

WHILE @@FETCH_STATUS=0 AND @monthFetch <> @monthInt
	BEGIN
		FETCH NEXT FROM curs INTO @monthFetch, @monthNameFetch
	END

CLOSE curs
DEALLOCATE curs
SELECT @monthNameFetch

Truly, I have never found a better use for a cursor than this one! Thanks!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources