Character Month Function... 1

[rheilman]

I have made this work, but the method I used feels really kludgy. I'm looking for a more elegant solution.

I have a numeric field that represents the month. I'd like to produce the word that represents that month. (ie...1 should produce 'January'). My solution is as follows:

DATENAME(MONTH, CAST(MonthField AS CHAR) + '/1/2000')

I don't particularly adding some arbitrary "date-like" text on the end of my given MonthField value. I'm looking for something akin to CMONTH() from FoxPro if it exists in T-SQL.

Thanks in advance!
Ray <><

 

[JamesLean]

I would use that method. Just make sure that the current login is using US English language or that you have used an explicit [tt]SET DATEFORMAT mdy[/tt] statement. If you want an unambiguous version:

SELECT DATENAME(mm, '2005' + RIGHT('0' + CAST(@m AS varchar), 2) + '01')

The alternative is to use a CASE expression:

SELECT CASE mth
  WHEN 1 THEN 'January'
  WHEN 2 THEN 'February'
  ...
  WHEN 12 THEN 'December'
  END

--James

 

[vongrunt]

Simple "good enough" hack:

datename( month, (MonthField-1)*31 )

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rheilman]

I love it! It's a much more elegant piece of math than my arbitrary text.

Thanks!
Ray <><