Month name from month number

[Viking]

I'd like a simple SQL statement that returns the month name from a month number. For example, if month number = 1, I'd like 'January', if month number = 12, I'd like 'December'.

I found on an SQL forum somewhere the following statement, and although I don't understand it, I hoped that it would work...

           Exec sql
             set :monthName = DateName(month, DateAdd(month,:myMonthNumber,-1));

But alas, it does not seem to work. Any idea what's wrong with this, or how to do what I want? I don't have a complete date, just a month number. (I think there's a way to get the month name from a date, so I suppose I could create a date of myMonthNumber/1/2014 and get the month name, but is there a better way?)

Thanks!

[TedHolt]

Re: Month name from month number

DB2 for i has a function to return the name of a day of the week, but I don't know of anything to return the month name.

I'd create a table with 12 rows in it, keyed on month number, with a month-name column. I'd join to that table to retrieve the month name.

Another option is to write a function that accepts an argument (it could be a month number from 1 to 12 or a date, from which you extract the month number) and return the month name.

The latter approach is probably faster, but I prefer to store data in tables, not in functions.

[Viking]

Re: Month name from month number

Currently I have defined a DS as follows:

     D months          ds
     d                                9a   Inz('January')
     d                                9a   Inz('February')
     d                                9a   Inz('March')
     d                                9a   Inz('April')
     d                                9a   Inz('May')
     d                                9a   Inz('June')
     d                                9a   Inz('July')
     d                                9a   Inz('August')
     d                                9a   Inz('September')
     d                                9a   Inz('October')
     d                                9a   Inz('November')
     d                                9a   Inz('December')
     d  monthNames                    9a   Dim(12) Overlay(months)

Then I get the month name with:

monthname = monthNames(monthNumber);

This works fine of course. I was just thinking maybe there was an SQL statement that could do it in one line.

Your idea of a table that can be joined is good too and I will do that if I need the month name within the SQL select in the future. Thanks for your reply.

[B.Hauser]

Re: Month name from month number

If you really want to do it with SQL

Exec SQL 
      Set :YourMonthName = MonthName('2014-' concat VarChar(:NumMont) concat '-01');

Birgitta

[Jim_IT]

Re: Month name from month number

Brigitta,

you just beat me to it.

Exec SQL
      Set :MonthName = MONTHNAME(Date('2000-12-01') + :myMonthNumber Months)

Jim

[Viking]

Re: Month name from month number

I used Birgitta's example above and it works well - thanks.

[TedHolt]

Re: Month name from month number

DB2 for i has a function to return the name of a day of the week, but I don't know of anything to return the month name.

I truly learn something new every day. I am very grateful to all who post so much helpful information here.