Extracting month from date as 'MMM'

Database
Highlighted
Enthusiast

Extracting month from date as 'MMM'

How can i extract month from a date in the MMM format.
example 01-31-2008 I need to get an output 'JAN'

can we do this with out using case statement?

Thanks
7 REPLIES
Enthusiast

Re: Extracting month from date as 'MMM'

select current_date (format 'MMM');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Date
----
Jan
Enthusiast

Re: Extracting month from date as 'MMM'

jim,
i tried it and i was still getting the date 01/04/2007
has it got anything to do with the database settings or the teradata version
we are currently using v2r5 and i issues this sql on queryman.

Thanks
Enthusiast

Re: Extracting month from date as 'MMM'

My previous suggestion only works in BTEQ. Normally, output formatting is controlled by the client program, but you can use the CAST operator to force the Teradata server do the conversion.

For example:

sel cast ((cast(current_date as format 'mmm')) as char(3));

Enthusiast

Re: Extracting month from date as 'MMM'

Thank you very much JIM.
Enthusiast

Re: Extracting month from date as 'MMM'

Yes,
Bteq and SQL assistant may have different result of the same sql,
Just because bteq use CLI and SQL Assistant use odbc,
but if you use sql for etl, you'd better try sqls in bteq
N/A

Re: Extracting month from date as 'MMM'

In SQL Assistant we can use: 


SELECT Current_Date(FORMAT 'MMM') (CHAR(3)) AS MonthName


Teradata Employee

Re: Extracting month from date as 'MMM'

This is also available. 


SELECT TO_CHAR(CURRENT_DATE, 'Mon');