Formatting A Date but keeing the value a Date Type

[campbere]

I would like to format a date value but keep it as a date type. I have a date column that displays 2000-12-06:00:00:00.00. I would like to display 12-06-2000.

I have tried to_char and substr but that makes the fields a string type. Can someone help?

 

[karluk]

In general you can't format a date. Formatting is for purposes of output only. It's impossible to modify the internal storage formats of dates.

Your best chance to get what you want is to alter your session to the appropriate nls_date_format. Then subsequent queries should display the date in the format you desire:

SQL> alter session set nls_date_format = 'mm-dd-yyyy';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
08-27-2001

 

[kindus]

yes, you have to set the date format in the session level as Karluk suggested or if you want a fixed format for all sessions i.e. for the instance without having to set everytime, then you may configure init.ora parameter in the format you want.

e.g. make the following entry in your init.ora file and bounce the database.

nls_date_format = mm/dd/yyy

 

[carp]

In your case, you would probably want
nls_date_format = mm-dd-yyyy

 

[uparna]

Hi,
In case you want to avoid changing the system variables a simple function trunc() will help you . You can use this while displaying and/or while inserting the data . The format is given in th efollowing example :
select trunc(datacolumn_name) from tablename ;
this will give you the date without the trailing time parameters.

Hope this helped you. Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[carp]

Something odd here. If you have a display of
2000-12-06:00:00:00.00
then you are not looking at an Oracle DATE datatype.
Oracle's DATE would only allow you to see
2000-12-06:00:00:00
since it only goes to a precision of Seconds. So you may be looking at a character string to begin with.

 

[uparna]

Carp ,
Oracle goes up to the precision of milliseconds and not seconds.......for eg. in your format statement , even hh/mi/ssss works........ obviously indicating that precision of milliseconds is also possible. I am sure that this is not a version problem as even the previous versions support this.

Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[sem]

Uparna, I'm not Carp, but Oracle does suport only seconds precission for DATE datatype. Just check the result of applying this mask: it doubles the seconds part.
Though there are several techniques to get higher precission, but not with DATE datatype.