Need to format date to ccyymmdd in Select in Oracle?

MIKE
MIKE used Ask the Experts™
on
Experts,

I need to format a column using SYSDATE to display like this:

ccyymmdd

What is the syntax to use?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
use to_char to make it a string.  Use any format mask you want:

select to_char(sysdate,'yyyymmdd') from dual;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
looks like 'ccyymmdd' is also allowed.

Check the docs for the allowed format values:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34510
johnsoneSenior Oracle DBA
Commented:
This can also be controlled at a larger level by setting NLS_DATE_FORMAT.

At the session level - ALTER SESSION SET NLS_DATE_FORMAT='CCYYMMDD';
At the database level - ALTER SYSTEM SET NLS_DATE_FORMAT='CCYYMMDD' scope=both;
In the environment - set NLS_DATE_FORMAT environment variable, syntax varies by OS
If client is windows in the registry

It can also be set in the login.sql or glogin.sql scripts, but that is not as common.
awking00Information Technology Specialist
Commented:
Be careful using that format. The cc will give you the century which, in the case of today, is the 21st century.

SQL> select to_char(sysdate,'ccyymmdd') from dual;

TO_CHAR(
--------
21110804

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