-
How to change format date
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
-
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.
Code:
TO_CHAR (date_column, 'DD/MM/YYYY')
-
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.
Code:
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?
-
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.
Code:
TO_CHAR (TO_DATE (string_column, 'DD/MM/YYYY'), 'MM/DD/YYYY')
-
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|