convert date to character 'YYYYMM' with substring and extract function 1

[titio]

What is the correct sql syntax for retreiving a character month column like 'YYYYMM' from a date ?

i try this but no success

SUBSTRING ( EXTRACT YEAR from date || EXTRACT MONTH from date 1 for 6 ) ????????????

 

[TeraMarv]

Try this:-

select trim(extract(year from date))||trim(extract(month from date) (format '99'));

 

[dunko]

or alternatively from BTEQ/ODBC:

select 
  data1,
  data2,
  cast((datefield (format 'YYYYMM')) as char(6)) as YearMonth
from
  sourcetbl;

Note: The cast to char(6) is only required if you are retrieving data via ODBC (if omitted it just comes across as the date field unchanged since it is converted into ODBC datatypes and the format is ignored)