Converting 2 digit to month name

Database
Enthusiast

Converting 2 digit to month name

How do I convert a 2 digit field (01, 02, 03, etc...) to a name month. So for example I need to convert '01' to JANUARY.

 

The month_dt field is a varchar(2).

 

I tried to_char(month_dt, 'MONTH') but getting error.

6 REPLIES
Teradata Employee

Re: Converting 2 digit to month name

You can do it as a CASE operation, but, considering the wordiness of it, I would create an SQL function for it.  You could put the function in the database where the query will be run, or in any database the user has execute privileges in.  For example,

 

REPLACE FUNCTION TO_MONTH_NAME( Digits VARCHAR(2) )
RETURNS VARCHAR(9)
LANGUAGE SQL CONTAINS SQL RETURNS NULL ON NULL INPUT
DETERMINISTIC COLLATION INVOKER INLINE TYPE 1
RETURN case Digits
    when '01' then 'January'
    when '02' then 'February'
    when '03' then 'March'
    when '04' then 'April'
    when '05' then 'May'
    when '06' then 'June'
    when '07' then 'July'
    when '08' then 'August'
    when '09' then 'September'
    when '10' then 'October'
    when '11' then 'November'
    when '12' then 'December'
    else ''
    end
;

 

Then you can just: Select ..., to_month_name(<month-digits>), ...

If the function is not in the same database as the views you are using then you have to qualify the function name: Select ..., <function-databasename>.to_month_name(<month-digits>), ...

Enthusiast

Re: Converting 2 digit to month name

Thanks for the response. I checked and I don't have create function privs. so I will need to come up with an alternative.

Enthusiast

Re: Converting 2 digit to month name

The existing code in DB2/SAS taking a 2 digit value in a month_dt field and setting it to a month name also checking if there is a spanish indicator. The &yes_year = 2016

 

        case upper(a.lang_cd)
        when 'ENG' then to_char(&yes_year||'-'||b.month_dt||'-01','MONTH')
        when 'ESL' then
                   case month(date(&yes_year||'-'||b.month_dt||'-01')) when 1 then 'ENERO' when 2 then 'FEBRERO' when 3 then 'MARZO'
                        when 4 then 'ABRIL' when 5 then 'MAYO' when 6 then 'JUNIO' when 7 then 'JULIO'
                        when 8 then 'AGOSTO' when 9 then 'SEPTIEMBRE' when 10 then 'OCTUBRE'
                        when 11 then 'NOVIEMBRE' when 12 then 'DICIEMBRE' end
         else upper(monthname(date(&yes_year||'-'||b.month_dt||'-01')))
        end as month_name,

Teradata Employee

Re: Converting 2 digit to month name

How can you have any fun without create-function privilege?  Oh well, you have to do it the hard way, similar to what you show here:

 

Select ...
,case b.month_dt
    when '01' then 'January'
    when '02' then 'February'
    when '03' then 'March'
    when '04' then 'April'
    when '05' then 'May'
    when '06' then 'June'
    when '07' then 'July'
    when '08' then 'August'
    when '09' then 'September'
    when '10' then 'October'
    when '11' then 'November'
    when '12' then 'December'
    else ''
    end
, ...

 

However, I don't know of any way to check the language context in Teradata.  Also, the overall context in the example you show doesn't make sense to me - why does it go to all the trouble to create a date from the month and then extract the month?!?  But whatever - you don't have to do that.  If b.month_dt is a varchar(2) then the above will work for Inglés
.

Enthusiast

Re: Converting 2 digit to month name

Yeah not much fun here! :(

 

I will try that. I also might derive a full date field in the source tables and pass that into the case statement.

 

Thanks again for the help!

Teradata Employee

Re: Converting 2 digit to month name

A internationalized approach is to add a FORMAT clause for the DateTime type with the 'M4' formatting directive for long month names and 'M3' for the shorter month names. For details, see page 284 of: http://www.info.teradata.com/download.cfm?ItemID=1007190.

 

If you are not getting Spanish month names, then the SDF needs to change (see “SDF file” and “Teradata Locale Definition Utility (tdlocaledef)” in Utilities). As of Teradata Database 16.0, the SDF is a system level locale data definiton. If you need support for more than one locale, submit an enhancment request to Teradata for the database to support DateTIime locale data at the session level.

 

Thanks,

 

-Dave