extract month from date as string mm

Question

I am trying to get the month from a date column

EXTRACT(MONTH FROM date) Month

It gives me a number but I need a string like JAN, FEB, etc.

What is the SQL to accomplish that?

Answer 1

There is no built-in function in Firebird that will immediately yield a month name. If you want that, you'll need to write it yourself, for example using a combination of extract and decode:

decode(
    extract(month from datevalue),
    1, 'JAN',
    2, 'FEB',
    3, 'MAR',
    4, 'APR',
    5, 'MAY',
    6, 'JUN',
    7, 'JUL',
    8, 'AUG',
    9, 'SEP',
    10, 'OCT',
    11, 'NOV',
    12, 'DEC')

If you use Firebird 3, then you can create a PSQL function for this:

create function monthname(datevalue date) returns char(3)
as
begin
    return decode(
            extract(month from datevalue),
            1, 'JAN',
            2, 'FEB',
            3, 'MAR',
            4, 'APR',
            5, 'MAY',
            6, 'JUN',
            7, 'JUL',
            8, 'AUG',
            9, 'SEP',
            10, 'OCT',
            11, 'NOV',
            12, 'DEC');
end

Alternatively, you could try:

case extract(month from datevalue)
   when 1 then 'JAN'
   when 2 then 'FEB'
   -- etc
   when 12 then 'DEC'
end