how to update the datetime format in oracle 12c

What is the format of the date column in the database?

if the data is text in dd/mm/yy format and you want to produce text formatted as
DD-MON-YYYY  HH24:MM:SS

then try this...

select to_char(to_date(yourdate,'dd/mm/rr'),'DD-MON-YYYY  HH24:MI:SS') from yourtable


note,  I change MM to MI in your time portion to return minutes instead of months

Are you trying to use Oracle with Crystal Reports?

mlmcc

First what is the data type of the column that you are looking at?  DATE or TIMESTAMP or VARCHAR or CHAR?

If DATE, then this is a pretty good explanation how the date is stored -> http://www.ixora.com.au/notes/date_representation.htm   This is not version dependent.  They have been store this way on every version that I have ever worked with.

As far as the string representation when selected out of the database, that is dependent on the setting of NLS_DATE_FORMAT (assuming DATE datatype).  While you can change this on the database side, it is a client setting and is easily overridden with an ALTER SESSION or registry/environment setting, or a few other ways.  You cannot rely on the client not changing the value of the date formats.

The only true way to control the format is to select the data with a TO_CHAR and force the format there.  At that point it is a string when it gets to the client and the date formats no longer apply.

Thanks for your reply

You're welcome ;-)