Correct date format in sql developer

sam2929
sam2929 used Ask the Experts™
on
Hi,
I have date_created datatype date showing in sql developer as 06-09-29 can I display it in yyyy-mm-dd format.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
select to_char(date_created,'yyyy-mm-dd') from your_table;


you can also set your session's format option but that will format all dates in all queries this way which may not be what you want (if, for example, some dates have hours,minutes,seconds populated with non-zero values)

alter session set nls_date_format='yyyy-mm-dd';

Author

Commented:
I did alter session now date coming like 006-10-09
Mark GeerlingsDatabase Administrator

Commented:
You apparently missed a "y".  You need four of them as sdstuber suggested.  You apparently executed this (with just three "y" characters):

alter session set nls_date_format='yyy-mm-dd';

Correct version is:
alter session set nls_date_format='yyyy-mm-dd';

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial