Thread: Convert number of month to name of month

How do I build a query that takes a field containing the number of the
month (1, 2, ..12) and displays it as the name of the month (Jan, Feb,
..Dec)?

For example:
1 as Jan
2 as Feb
etc.

On Tue, 2002-07-30 at 07:02, Matthew Lock wrote:
> How do I build a query that takes a field containing the number of the
> month (1, 2, ..12) and displays it as the name of the month (Jan, Feb,
> ..Dec)?
>
> For example:
> 1 as Jan
> 2 as Feb
> etc.

In plain SQL, use CASE:

  SELECT CASE
           WHEN monthno = 1 THEN 'January'
           WHEN monthno = 2 THEN 'February'
           ...
         END AS month
    FROM ...

You could also write a function in PL/PGSQL...

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "All scripture is given by inspiration of God, and is
      profitable for doctrine, for reproof, for correction,
      for instruction in righteousness;"
                                     II Timothy 3:16

On Fri, Aug 02, 2002 at 06:59:43PM +0100, Oliver Elphick wrote:
> On Tue, 2002-07-30 at 07:02, Matthew Lock wrote:
> > How do I build a query that takes a field containing the number of the
> > month (1, 2, ..12) and displays it as the name of the month (Jan, Feb,
> > ..Dec)?
> >
> > For example:
> > 1 as Jan
> > 2 as Feb
> > etc.
>
> In plain SQL, use CASE:
>
>   SELECT CASE
>            WHEN monthno = 1 THEN 'January'
>            WHEN monthno = 2 THEN 'February'
>            ...
>          END AS month
>     FROM ...
>
> You could also write a function in PL/PGSQL...

 Too long :-)

test=# SELECT to_char(('2000-'|| 8 || '-01')::date , 'Month');
  to_char
-----------
 August

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz