Search code examples
sqloracle-databasepeoplesoft

Modify an existing to_char date format

Oracle SQL automatically converts my field D.START_DT to the following format:

TO_CHAR(D.START_DT,'YYYY-MM-DD')

Which makes it difficult for me to modify my own date format.

I've tried wrapping another TO_CHAR around it with no luck.

TO_CHAR(TO_CHAR(D.START_DT,'YYYY-MM-DD'), 'MM/DD')

And I've tried SUBSTR to select certain characters, with no luck. I think the hyphen is getting int he way.

SUBSTR(TO_CHAR(D.START_DT,'YYYY-MM-DD'), 6, 7) || '/' || SUBSTR(TO_CHAR(D.START_DT,'YYYY-MM-DD'), 9, 10)

What is the work around for this?

Solution

  • A DATE datatype has no format. When you see a date printed on a screen, there was something that APPLIED the format you see. Could be a "default" in the program you are using (like SQL Developer) or your NLS setting, etc. But, a DATE datatype has no format. So, you have complete control over the format you see on screen.

    The simplest is to use the TO_CHAR function:

    select TO_CHAR(D.START_DT,'YYYY') from dual;

    returns just the four digit year.

    See TO_CHAR date format options.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements004.htm#CDEHIFJA

    You should always supply the format in your code and not rely on some other "default" to supply it.