sql - Can't convert timestamp to date -

i'm trying convert timestamp saved in table date, reason, returns same date, or gives me strange error:

ora-01830: date format picture ends before converting entire input string

here have tried no success: type of change_date field timestamp(6)

select to_date (change_date, 'yyyy-mon-dd') test gives me above error.

select cast(systimestamp date) test no change in format.

select to_date(to_char(change_date)) test no change.

select to_char(to_date(change_date, 'dd-mon-yy')) test no luck.

and few other tricks can't remember, returns same format - 16-dec-14 07.59.24.097000 am

i need output date - example 16-dec-14

i know i'm missing small part here, i'm not able spot it. can give me push!

to truncate time part of date or timestamp value should use trunc function. default truncates time part, returning date time of midnight.

depending on database version can directly truncate timestamp, or may have cast date first, eg

trunc(change_date) 

or

trunc(cast(change_date date)) 

as mentioned in comment, dates don't have formats. date-typed values, numbers, stored in specific binary format isn't affected locales or formats. formats come play when try convert date string or parse string date.

confusion arises because dates (and numbers) converted strings implicitly,eg display purposes, when comparing values of different types. in case database use default locale convert 1 type other before making comparison.

this implicit conversion causes people think dates , numbers have specific format when don't.