How to change format date

[raf]

Hi,
I've run this query:

select to_date(sysdate,'DD/MM/YYYY') DATE_TODAY
from dual

my output is:

DATE_TODAY
2/22/0005

but I'd like to get this output (with 'DD/MM/YYYY' format):

DATE_TODAY
22/2/2005

How Can I change the date format?
Must I change the format server-side?

Thanks

[padders]

NEVER use TO_DATE on a DATE (it is for converting strings to DATEs). To display a DATE in a required format use TO_CHAR with appropriate date mask, e.g.

TO_CHAR (date_column, 'DD/MM/YYYY')

[raf]

Originally posted by padders
NEVER use TO_DATE on a DATE (it is for converting strings to DATEs). To display a DATE in a required format use TO_CHAR with appropriate date mask, e.g.

TO_CHAR (date_column, 'DD/MM/YYYY')

ok, but if I've
to_date(sting_column,'DD/MM/YYYY') DATE_TODAY


with this output :

DATE_TODAY
2/22/0005

How Can I change it?

[padders]

If date was held in a VARCHAR2 / CHAR column in format DD/MM/YYYY (which incidentally is a VERY BAD IDEA) then you could convert it (on the fly) to a DATE using TO_DATE and the stored format and then TO_CHAR the result using the desired display format, e.g.

TO_CHAR (TO_DATE (string_column, 'DD/MM/YYYY'), 'MM/DD/YYYY')

[Lowaiy]

You can take an idea about your NLS_DATE_FORMAT by this statement:

*******************************************************************
select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
*******************************************************************

And if you want to change it you can use this statement:

*****************************************************************
ALTER SESSION SET NLS_DATE_FORMAT = [THE NEW NLS_DATE_FORMAT]

For Example:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'
*****************************************************************

Anyhow, I don't think that you're sure when execute TO_CHAR('DD-MM-YYYY', sysdate) you get the result in wrong way...