Is it possible to get the month name with only an integer representation of the number

Hi,

Is it possible to get the month name with only an integer representation of the number.

i.e January ,February..... the DATENAME only takes a date as a value.

thanks in advance

Yes, you can use a combination of the DATEADD and DATENAME functions for this. Something like this:

Code Snippet

select number,
datename(mm, dateadd(mm, number - 1, 0))
as monthName
from master.dbo.spt_values (nolock)
where name is null
and number > 0
and number < 13

/*
number monthName
--
1 January
2 February
3 March
4 April
5 May
6 June
7 July
8 August
9 September
10 October
11 November
12 December
*/

|||

Thank you that realy worked perfectly.

can you explain what is name =null

and what is

master.dbo.spt_values (nolock)

sorry for picking your brain I just want to understand it.

|||

Well, the MASTER.DBO.SPT_VALUES (NOLOCK) is a reference to the table that I used. This is an undocumented table and my use here is also undocumented and in general should NOT be propagated! Really, this is a case of me being lazy. You remember here earlier today? I suggested that you make a 12-entry inline table with values 1-12 for each month of the year. This is what I SHOULD be doing here. I am really doing the same thing with this particular reference only in this case my method is completely "unsafe" -- my solution is "quick and dirty" -- like REAL dirty.

The "NAME IS NULL" is just a method of making sure that I am narrowing the list of numbers that will be returned such that no number will appear more than once -- again, "quck and dirty". All I was trying to do was to quickly give you numbers 1-12 (in this case from a very dirty source) so that I could show you how to use the DATEADD and DATENAME functions to correspond to the numeric values for each month (GRRRR this KEYBOARD!!!). The (NOLOCK) portion is designating the "NOLOCK OPTIMIZER HINT" so that I do not incur any locks on the SPT_VALUES table.

I really should have explained this rather than leave you hanging to wander about it. Please forgive me for this infraction.

Kent

Please, someone add commentary to this.

|||

I thought about it a bit over night and realized that you might want to give a look to this article about the virtues of having a "table of numbers":

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

Since twice we have discussed queries that utilizes numbers 1-12, it might be appropriate to give this a look.

|||

Kent Nice work. I closed the thread.