Cast NUMBER to DATE. Error ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Try converting int to varchar and then varchar to date

select cast(cast(col_date as varchar(10)) as date)

Use the to_date() function:

select to_date(col_date, 'YYYYMMDD')
from school

That does an implicit conversion from number to string, but you can make it explicit:

select to_date(to_char(col_date), 'YYYYMMDD')
from school

Of course, it would be better to store your values as proper dates. You may have numbers which don't correspond to actual dates, and will need to decide how to handle those if you do.

Oracle's date datatype always has a time component, which will be set to midnight with this conversion. They have no intrinsic human-readable format - your client decides how to display, usually using your session NLS_DATE_FORMAT setting. You can change that with alter session, which will affect the display of all date values.

If you want to display the date as a string with a particularly format then you can reverse the process with the to_char() function:

select to_char(to_date(to_char(col_date), 'YYYYMMDD'), 'YYYY-MM-DD')
from school

If you only want it reformatted as a string, and don't need it as a real date at all, you could just format the number directly:

select to_char(col_date, 'FM0000G00G00', 'nls_numeric_characters='' -''')
from school

db<>fiddle

But either way, only do that for final display - leave it as an actual date (not string) for any processing, joins, storage etc.