Home » SQL & PL/SQL » SQL & PL/SQL » Date Format change (oracle 10g)
Date Format change [message #394543] Fri, 27 March 2009 13:30 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi Friends ,
I am getting a typical problem.

SQL> select sysdate from dual;

SYSDATE
-----------
3/25/2009 1

I want to change the date format.So I tried
SQL> ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';

Session altered

the problem still exists.
SQL> select sysdate from dual;

SYSDATE
-----------
3/25/2009 1

I have checked theNLS_SESSION_PARAMETERS also.
SQL> SELECT * from NLS_SESSION_PARAMETERS;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

17 rows selected



Kindly suggest how to fix it.


Re: Date Format change [message #394558 is a reply to message #394543] Fri, 27 March 2009 15:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is not something typical, this is something really weird.

Please run the following, and see what that gives as a result :

select to_char(sysdate,'DD/MM/YYYY') from dual;


And post your entire, formatted SQL*Plus session (including the version output of both the SQL*Plus client and the server.)

And which OS the client and the server runs.

Re: Date Format change [message #394559 is a reply to message #394543] Fri, 27 March 2009 15:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Only thing I can think of is that you have a local table called DUAL with a column called SYSDATE, datatype VARCHAR2(11) containing the value
3/25/2009 1
Re: Date Format change [message #394722 is a reply to message #394543] Sun, 29 March 2009 22:19 Go to previous message
bananaman
Messages: 71
Registered: March 2009
Member
there are various formats you can use for sysdate using the to_char function..below is the general syntax that you should use where the parameters inside the brackets [] are optional..

to_char( value, [ format_mask ], [ nls_language ] )

[ format_mask ] -- This is the format that will be used to convert value to a string.

[ nls_language ] -- This is the nls language used to convert value to a string.

Ex: --the code below would result to '2009/03/30' depending on your system date if you tried..
to_char(sysdate, 'yyyy/mm/dd');

Below are the formats you can use.. Hope this will help..
*DATE FORMATS
Parameter Explanation
YEAR Year, spelled out
YYYY 4-digit year
YYYYYY Last 3, 2, or 1 digit(s) of year.
IYYIYI Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.


*based on techonthenet.com

regards,
bananaman

Previous Topic: PLS-00103 error
Next Topic: how to specify dynamic cursorname in FETCH INTO statement
Goto Forum:
  


Current Time: Sat Dec 03 12:16:32 CST 2016

Total time taken to generate the page: 0.10625 seconds