URGENT- date format

[sonaliak]

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

[kmesser]

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 . . .

[Mara]

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.

[sonaliak]

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.

[SANJAY_G]

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

[sudip]

I think using NLS_DATE_FORMAT parameter in initializing files will be a safer option.

[PSoni]

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]

[jgun]

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..