sql - How do I display DATE in 'DD MON YYYY' format? -

i newbie oracle database programming , wish insert date (also display) in 'dd mon yyyy' format. (ps: involves insert event). data type (date or timestamp) suitable option me in order accomplish format? how supposed that? thanks.

a date column not have format.

so format use when inserting or updating data irrelevant displaying data (that's 1 of reasons why should never store date in varchar column).

any formatted output see date column in sql tool (e.g. sql*plus) applied tool. not part of data stored in column.

when providing date literal should either use to_date() function explicit format mask:

insert some_table (some_date_column) values (to_date('27-06-2014', 'dd-mm-yyyy')); 

i not recommend using formats written month names (27-jun-2014) when supplying date literal because also depend on nls settings of client computer , might produce (random) errors due different languages. using numbers more robust.

i prefer use ansi date literals because it's bit less typing:

insert some_table (some_date_column) values (date '2014-06-27'); 

the format ansi date (or timestamp) literal always iso format (yyyy-mm-dd).

when select data can display date in whatever format like. either using to_char() function (e.g. when using sql tool) or using functions programming language (the preferred way use inside application):

select to_char(some_date_column,'dd-mon-yyyy') some_table; 

note date data type in oracle (despite it's name) stores time. timestamp same thing higher precision (it includes milliseconds, whereas date data type stores seconds).