date Problem - Urgent

[SPN]

When I select sysdate from dual, I get only first 2 digits of the year portion.

eg:

select sysdate from dual;

05-JUN-20

Logically it should be either 05-JUN-01 OR 05-JUN-2001

NLS_DATE_FORMAT is set as DD-MON-RRRR ( for DD-MON-YYYY also the result is the same)

I would appreciate your suggestion.

SPN

[dknight]

Would this provide a solution? It works on my machine.

select to_char(sysdate, 'DD-MON-YYYY') from dual;

[SPN]

It's not working. Did you face similar probelm.

[marist89]

what does:
select to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;

show?

[jmodic]

What kind of tool are you using for those queries? Is it a kind of GUI tool that displays the results in a grid? Is it possible that the field for your result in this grid is too narrow, so that only part of the whole string is displayed?

What is the result of the following:

SELECT TO_CHAR(SYSDATE), LENGTH(TO_CHAR(SYSDATE)) FROM dual;

[dknight]

I do not have an NLS_DATE_FORMAT set, but I have no problems.

What is the result when you use RRRR or YYYY? Both of those work fine for me.

[tamilselvan]

First alter the session.
SQL> alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

Session altered.

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------------------
05-JUN-2001 11:36:47

[SPN]

Hi Friends,

Thanks to all. I tried all these but still the problem is the same. I am not using any GUI tool. It's coming on the OS prompt itself. It's on UNIX.

SPN

[denevge]

I know it may sound like a silly remark but

Have you checked the system time in unix using the 'date' command ?

Gert

[jm]

looks like your date field is truncated.

- exist from sqlplus and login again
- check your login file