David David - 7 months ago 21
SQL Question

Oracle SQL - Extract YYYYMM from YYYYMMDD, when column type is NUMBER(38)

I have a column named

, which type is

When I run a SELECT query I get Year-Month-Day results such as:

20161230, 20160531 or 20170330

What I hope to achieve is to limit the number of characters displayed to Year-Month only. So I would get the following:

201612, 201605 or 201703

I tired using functions like TO_CHAR or EXTRACT but with no success.


You can always divide by 100 to remove the last 2 digits. So something like this could work:

select to_char(trunc(dateoforder / 100))
  from tbl