Convert date month from a number to Jan, Feb, Mar, etc.

Database
Enthusiast

Convert date month from a number to Jan, Feb, Mar, etc.

I have two fields VintageYear and VintageMonth.  The VintageMonth datatype is number.  I have just started to use Teradata at work.  What function should I use for this conversion?  I have tried something similar to the following code: CASE MONTH VintageMonth WHEN 1 THEN VintageMonth END AS Jan.

6 REPLIES
Junior Contributor

Re: Convert date month from a number to Jan, Feb, Mar, etc.

You do it exactly the way you would do in any other DBMS :-)

CASE VintageMonth 
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
...
END

Dieter

Enthusiast

Re: Convert date month from a number to Jan, Feb, Mar, etc.

Thank you, I do have one more question in relation to this same topic.  I am breaking out the number of transactions for each month.  In Teradata SQL, would I use the Count function for each month listed under the CASE clause?  If so, could you provide a quick example?

Thanks,

Jeggert

Junior Contributor

Re: Convert date month from a number to Jan, Feb, Mar, etc.

Simply use the CASE and GROUP BY it:

SELECT
CASE VintageMonth
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
...
END A MonthName,
COUNT(*)
FROM tab
GROUP BY 1 -- or GROUP BY MonthName

Dieter

Enthusiast

Re: Convert date month from a number to Jan, Feb, Mar, etc.

Posting text removed based on formal request from Owner of the associated Intelectual Property.

Junior Contributor

Re: Convert date month from a number to Jan, Feb, Mar, etc.

No nested SELECT needed :-)

Posting text removed based on formal request from Owner of the associated Intelectual Property.

Enthusiast

Re: Convert date month from a number to Jan, Feb, Mar, etc.

Thanks, I wasn't too far off.  I appreciate the help!