-
I needed urgent help on this so I am posting my question in Administration section which seems to be used more.
Here are the queries:
SQLWKS> select to_char(sysdate,'dd-mon-yyyy') from dual;
TO_CHAR(SYS
-----------
22-feb-2001
1 row selected.
SQLWKS> select to_date(sysdate,'dd-mon-yyyy') from dual;
TO_DATE(SYSDATE,'DD-
--------------------
22-FEB-01
1 row selected.
When I do to_date the year is always represented in 2 YY and not in 4 YYYY which I am trying to format to.
When I do to_char the output is a string but with the format i want 4 YYYY. But I don't want output as a string I want it as date. How can I do this ????
In other words I want to get the date formatted so that year is represented in 4 digits and not in 2 digits.
I cannot use to_char as it converts date to string.
I cannot change NLS_date format which will make it complex( in handling customer and not query wise)
Is their any way to get this ???
SYSDATE is just a ex. We will be selecting actual date from a table.
Sonali
Sonali
-
Could you display the value after selecting it as a string, then process it using it as a date? For example, select to_char(sysdate, 'dd-mon-yyyy') into X from dual. Then in a subsequent line of code, use X as a date, such as Y := to_date(X);
I'll try to be more specific if you post how you are using it. For example, if it's in forms or a stored procedure . . .
-
It looks like the parameter NLS_DATE_FORMAT is set to DD-MON-YY. You could verify this by selecting
select sysdate from dual ;
If you are under NT, go to registry (start regedit.exe utility). Then go to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOMEi where HOMEi is the home in which Oracle server or client rezides. Go to EDIT->String Value and name it NLS_DATE_FORMAT. Set the value for this variable to DD-MON-YYYY.
Close SQL Worksheet and start it again to test the new format. It should be 22-FEB-2001.
-
Nup, you do to_date and it converts it back to 2 YY, 2 digit year.
SQLWKS> select to_char(sysdate, 'DD-MON-YYYY') from dual;
TO_CHAR(SYS
-----------
22-FEB-2001
1 row selected.
SQLWKS> select to_date(to_char(sysdate, 'DD-MON-YYYY')) from dual;
TO_DATE(TO_CHAR(SYSD
--------------------
22-FEB-01
1 row selected.
I have no problem in using a variable to hold the value.
I think setting NLS_date format is the only option left.
Sonali
-
If you do not want to change the NLS_DATE_FORMAT for the database you can change it only for your session.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
SELECT SYSDATE FROM DUAL;
SYSDATE
--------------
23-FEB-2001
Sanjay
-
I think using NLS_DATE_FORMAT parameter in initializing files will be a safer option.
-
Yes,
set SERVEROUT ON
and run following procedure
declare
x Varchar2(11);
y date;
begin
select to_char(sysdate,'dd-mon-yyyy') into x
from dual;
y :=to_date(x);
dbms_output.put_line(y);
end;
Pitamber Soni
-----------------------------
[QUOTE][i]Originally posted by kmesser [/i]
[B]Could you display the value after selecting it as a string, then process it using it as a date? For example, select to_char(sysdate, 'dd-mon-yyyy') into X from dual. Then in a subsequent line of code, use X as a date, such as Y := to_date(X);
I'll try to be more specific if you post how you are using it. For example, if it's in forms or a stored procedure . . . [/B][/QUOTE]
-
hi,
if u want to display the year always in 4y means.
just the the NLS_DATE_FORMAT..
Oracle have 3 parameters for that.
for session.
for instance.
for Database.
ASk the DBA to change this..
Regds,
Jgun..
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
|